Python - Insert 300 variables into SQLite
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 '18 at 4:57
MinoMino
31
add a comment |
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 '18 at 4:57
MinoMino
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 '18 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 '18 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 '18 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 '18 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 '18 at 5:11
add a comment |
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 '18 at 4:57
MinoMino
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 '18 at 4:57
MinoMino
31
asked Nov 11 '18 at 4:57
MinoMino
31
asked Nov 11 '18 at 4:57
MinoMino
31
asked Nov 11 '18 at 4:57
MinoMino
31
asked Nov 11 '18 at 4:57
MinoMino
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 '18 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 '18 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 '18 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 '18 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 '18 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 '18 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 '18 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 '18 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 '18 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 '18 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 '18 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 '18 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 '18 at 5:03
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 '18 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 '18 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 '18 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 '18 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 '18 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 '18 at 5:11
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 '18 at 5:11
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 5:10
MureinikMureinik
181k22131200
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 5:10
MureinikMureinik
181k22131200
add a comment |
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 '18 at 5:10
MureinikMureinik
181k22131200
add a comment |
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 '18 at 5:10
MureinikMureinik
181k22131200
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 '18 at 5:10
MureinikMureinik
181k22131200
answered Nov 11 '18 at 5:10
MureinikMureinik
181k22131200
answered Nov 11 '18 at 5:10
MureinikMureinik
181k22131200
answered Nov 11 '18 at 5:10
MureinikMureinik
181k22131200
181k22131200
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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 '18 at 5:01
Write code that creates the insert query instead of typing it out by hand.
– Shawn
Nov 11 '18 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 '18 at 5:05
Usually you would write one row per sensor value instead of having 300 columns in your database.
– Klaus D.
Nov 11 '18 at 5:05
What are the actual names of your columns?
– Tim Biegeleisen
Nov 11 '18 at 5:11