Python and SQLite: insert into table

user_246365 0
Viewed 181837

I have a list that has 3 rows each representing a table row:

>>> 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 "--->",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.

insert list sqlite python
6 Answers
conn = sqlite3.connect('/path/to/your/sqlite_file.db')
c = conn.cursor()
for item in my_list:
  c.execute('insert into tablename values (?,?,?)', item)
Dominic_Rodger_209720

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_2217940

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_109630540 
#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_114473940
Related