Python and SQLite: insert into table
Solution 1
conn = sqlite3.connect('/path/to/your/sqlite_file.db')
c = conn.cursor()
for item in my_list:
c.execute('insert into tablename values (?,?,?)', item)
Solution 2
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()
Solution 3
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.
Solution 4
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)
webminal.org
Free and Open Source programmer. Projects include: FileSystem Projects and Free Online Linux Terminal
Updated on July 08, 2022Comments
-
webminal.org almost 2 years
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.