Python - Insert 300 variables into SQLite
up vote
-1
down vote
favorite
I am receiving 300 values from heat sensor every minute. These 300 values needs to be inserted, as they are received every minute, into SQLite database.
I have created 302 rows in SQLite database with the first column being S_ID and second column being timestamp. Here, S_ID is being auto-incremented each time a row is added and the default value of timestamp column is the current system time. I have programmed such that I receive 300 heat sensor values every minute, put all 300 values in a list named data and insert data into database. Now, I need to know how I can write executemany statement without writing all 300 column names and ? below.
data = [(300, 2, 4, ..., 5.5)] #these are 300 values that are inserted into a list when received from heat sensor
c.executemany('INSERT INTO heat_table (col3, col4, ..., col302) VALUES (?, ?, ..., ?)', data)
python database sqlite insert bulkinsert
asked Nov 11 at 4:57
Mino
31
add a comment |
up vote
-1
down vote
favorite
I am receiving 300 values from heat sensor every minute. These 300 values needs to be inserted, as they are received every minute, into SQLite database.
I have created 302 rows in SQLite database with the first column being S_ID and second column being timestamp. Here, S_ID is being auto-incremented each time a row is added and the default value of timestamp column is the current system time. I have programmed such that I receive 300 heat sensor values every minute, put all 300 values in a list named data and insert data into database. Now, I need to know how I can write executemany statement without writing all 300 column names and ? below.
data = [(300, 2, 4, ..., 5.5)] #these are 300 values that are inserted into a list when received from heat sensor
c.executemany('INSERT INTO heat_table (col3, col4, ..., col302) VALUES (?, ?, ..., ?)', data)
python database sqlite insert bulkinsert
asked Nov 11 at 4:57
Mino
31
Best practice is to always explicitly list out the columns involved in an insert. While there might be a way to do this without naming columns, it could break if your table structure were ever to change.
– Tim Biegeleisen
Nov 11 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 at 5:03
Or use a database design that doesn't need 300 columns. Something with one row per sensor per minute?
– Shawn
Nov 11 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 at 5:11
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
I am receiving 300 values from heat sensor every minute. These 300 values needs to be inserted, as they are received every minute, into SQLite database.
I have created 302 rows in SQLite database with the first column being S_ID and second column being timestamp. Here, S_ID is being auto-incremented each time a row is added and the default value of timestamp column is the current system time. I have programmed such that I receive 300 heat sensor values every minute, put all 300 values in a list named data and insert data into database. Now, I need to know how I can write executemany statement without writing all 300 column names and ? below.
data = [(300, 2, 4, ..., 5.5)] #these are 300 values that are inserted into a list when received from heat sensor
c.executemany('INSERT INTO heat_table (col3, col4, ..., col302) VALUES (?, ?, ..., ?)', data)
python database sqlite insert bulkinsert
asked Nov 11 at 4:57
Mino
31
I am receiving 300 values from heat sensor every minute. These 300 values needs to be inserted, as they are received every minute, into SQLite database.
I have created 302 rows in SQLite database with the first column being S_ID and second column being timestamp. Here, S_ID is being auto-incremented each time a row is added and the default value of timestamp column is the current system time. I have programmed such that I receive 300 heat sensor values every minute, put all 300 values in a list named data and insert data into database. Now, I need to know how I can write executemany statement without writing all 300 column names and ? below.
data = [(300, 2, 4, ..., 5.5)] #these are 300 values that are inserted into a list when received from heat sensor
c.executemany('INSERT INTO heat_table (col3, col4, ..., col302) VALUES (?, ?, ..., ?)', data)
python database sqlite insert bulkinsert
python database sqlite insert bulkinsert
asked Nov 11 at 4:57
Mino
31
asked Nov 11 at 4:57
Mino
31
asked Nov 11 at 4:57
Mino
31
asked Nov 11 at 4:57
Mino
31
asked Nov 11 at 4:57
Mino
31
31
Best practice is to always explicitly list out the columns involved in an insert. While there might be a way to do this without naming columns, it could break if your table structure were ever to change.
– Tim Biegeleisen
Nov 11 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 at 5:03
Or use a database design that doesn't need 300 columns. Something with one row per sensor per minute?
– Shawn
Nov 11 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 at 5:11
add a comment |
Best practice is to always explicitly list out the columns involved in an insert. While there might be a way to do this without naming columns, it could break if your table structure were ever to change.
– Tim Biegeleisen
Nov 11 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 at 5:03
Or use a database design that doesn't need 300 columns. Something with one row per sensor per minute?
– Shawn
Nov 11 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 at 5:11
Best practice is to always explicitly list out the columns involved in an insert. While there might be a way to do this without naming columns, it could break if your table structure were ever to change.
– Tim Biegeleisen
Nov 11 at 5:01
Best practice is to always explicitly list out the columns involved in an insert. While there might be a way to do this without naming columns, it could break if your table structure were ever to change.
– Tim Biegeleisen
Nov 11 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 at 5:03
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 at 5:03
Or use a database design that doesn't need 300 columns. Something with one row per sensor per minute?
– Shawn
Nov 11 at 5:05
Or use a database design that doesn't need 300 columns. Something with one row per sensor per minute?
– Shawn
Nov 11 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 at 5:11
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 at 5:11
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
I'd create these names with list comprehensions and then join them:
query = ('INSERT INTO heat_table (' +
', '.join('col%d' % i for i in range(3, len(data) + 3)) +
') VALUES (' +
', '.join('?' * len(data)) +
')')
answered Nov 11 at 5:10
Mureinik
175k21125192
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
I'd create these names with list comprehensions and then join them:
query = ('INSERT INTO heat_table (' +
', '.join('col%d' % i for i in range(3, len(data) + 3)) +
') VALUES (' +
', '.join('?' * len(data)) +
')')
answered Nov 11 at 5:10
Mureinik
175k21125192
add a comment |
up vote
0
down vote
accepted
I'd create these names with list comprehensions and then join them:
query = ('INSERT INTO heat_table (' +
', '.join('col%d' % i for i in range(3, len(data) + 3)) +
') VALUES (' +
', '.join('?' * len(data)) +
')')
answered Nov 11 at 5:10
Mureinik
175k21125192
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
I'd create these names with list comprehensions and then join them:
query = ('INSERT INTO heat_table (' +
', '.join('col%d' % i for i in range(3, len(data) + 3)) +
') VALUES (' +
', '.join('?' * len(data)) +
')')
answered Nov 11 at 5:10
Mureinik
175k21125192
I'd create these names with list comprehensions and then join them:
query = ('INSERT INTO heat_table (' +
', '.join('col%d' % i for i in range(3, len(data) + 3)) +
') VALUES (' +
', '.join('?' * len(data)) +
')')
answered Nov 11 at 5:10
Mureinik
175k21125192
answered Nov 11 at 5:10
Mureinik
175k21125192
answered Nov 11 at 5:10
Mureinik
175k21125192
answered Nov 11 at 5:10
Mureinik
175k21125192
175k21125192
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53245977%2fpython-insert-300-variables-into-sqlite%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Best practice is to always explicitly list out the columns involved in an insert. While there might be a way to do this without naming columns, it could break if your table structure were ever to change.
– Tim Biegeleisen
Nov 11 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 at 5:03
Or use a database design that doesn't need 300 columns. Something with one row per sensor per minute?
– Shawn
Nov 11 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 at 5:11