Python and SQLite insert into table

Question

I have a list that has 3 rows each representing a table row    gt  gt  gt  print list  laks 444 M   kam 445 M   kam 445 M    How to insert this list into a table   My table structure is    tablename name varchar 100   age int  sex char 1     Or should I use something other than list   Here is the actual code part       for record in self server          print  --- gt   record         t record         self cursor execute  insert into server server  values       t 0              self cursor execute  insert into server id  values       t 1             self cursor execute  insert into server status  values       t 2        Inserting the three fields separately works  but using a single line like  self cursor execute  insert into server server c id status  values           t 0     t 1     t 2      or  self cursor execute  insert into server server c id status  values           t     does not

User · Answer

This will work for a multiple row df having the dataframe as df with the same name of the columns in the df as the db   tuples   list df itertuples index False  name None    columns list   df columns tolist   marks        for   in columns list  columns list   f        join columns list      marks   f        join marks       table name    whateveryouwant   c executemany f INSERT OR REPLACE INTO  table name  columns list  VALUES  marks    tuples  conn commit

User · Answer

The Best way is to use  fStrings   very easy and powerful in python3      Format  f your-string      For Example   mylist   laks  444  M    cursor execute f INSERT INTO mytable VALUES    mylist 0      mylist 1      mylist 2         THATS ALL   EASY    You can use it with for loop

User · Answer

conn   sqlite3 connect   path to your sqlite file db   c   conn cursor   for item in my list    c execute  insert into tablename values           item

User · Answer

Adapted from http   docs python org library sqlite3 html     Larger example for t in    2006-03-28    BUY    IBM   1000  45 00               2006-04-05    BUY    MSOFT   1000  72 00               2006-04-06    SELL    IBM   500  53 00                   c execute  insert into stocks values               t

User · Answer

there s a better way     Larger example rows      2006-03-28    BUY    IBM   1000  45 00             2006-04-05    BUY    MSOFT   1000  72 00             2006-04-06    SELL    IBM   500  53 00   c executemany  insert into stocks values               rows  connection commit

User · Answer

Not a direct answer  but here is a function to insert a row with column-value pairs into sqlite table   def sqlite insert conn  table  row       cols        join        format col  for col in row keys        vals        join       format col  for col in row keys        sql    INSERT INTO   0     1   VALUES   2    format table  cols  vals      conn cursor   execute sql  row      conn commit     Example of use   sqlite insert conn   stocks              created at    2016-04-17            type    BUY            amount   500           price   45 00     Note  that table name and column names should be validated beforehand

[python] Python and SQLite: insert into table

Examples related to python

Examples related to sqlite

Examples related to list

Examples related to insert