Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
Having a
list
that has 3 rows each representing a table row:
row1 = [laks,444,M]
row2 = [kam,445,M]
row3 = [kam,445,M]
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 these 2 example doesn't work.
self.cursor.execute("insert into server(server,c_id,status) values (?,?,?)",(t[0],),(t[1],),(t[2],))
self.cursor.execute("insert into server(server,c_id,status) values (?,?,?)",(t),)
–
# 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()
–
–
–
conn = sqlite3.connect('/path/to/your/sqlite_file.db')
c = conn.cursor()
for item in my_list:
c.execute('insert into tablename values (?,?,?)', item)
–
–
–
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.
# 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)
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()
Adding to the provided answer, I think there are even better improvements.
1) Using Dictionary instead of list
Why?, better readability, maintainability and possibly speed (getting from a dictionary is way faster than access data via list indexing, in large list)
import sqlite3
connection: sqlite3.Connection = sqlite3.connect(":memory:")
connection.row_factory = sqlite3.Row # This will make return a dictionary like object instead of tuples
cursor: sqlite3.Cursor = connection.cursor()
# create db
cursor.execute("CREATE TABLE user(name VARCHAR[100] UNIQUE, age INT, sex VARCHAR[1])")
data = (
{"name": "laks", "age": 444, "sex": "M"},
{"name": "kam", "age": 445, "sex": "M"},
{"name": "kam2", "age": 445, "sex": "M"},
cursor.executemany("INSERT INTO user VALUES(:name, :age, :sex)", data)
cursor.execute("SELECT * FROM user WHERE age >= ?", (445, ))
users = cursor.fetchall()
for user in users:
print(user["name"], user["age"], user["sex"])
Output
kam 445 M
kam2 445 M
2) Use a context manager to automatically commit - rollback
This is way better to handle transaction automatically and rollback.
import sqlite3
connection: sqlite3.Connection = sqlite3.connect(":memory:")
connection.row_factory = sqlite3.Row # This will make return a dictionary like object instead of tuples
cursor: sqlite3.Cursor = connection.cursor()
# create db
# con.rollback() is called after the with block finishes with an exception,
# the exception is still raised and must be caught
with connection:
cursor.execute("CREATE TABLE user(name VARCHAR[100] UNIQUE, age INT, sex VARCHAR[1])")
except sqlite3.IntegrityError as error:
print(f"Error while creating the database, {error}")
data = (
{"name": "laks", "age": 444, "sex": "M"},
{"name": "kam", "age": 445, "sex": "M"},
{"name": "kam2", "age": 445, "sex": "M"},
# con.rollback() is called after the with block finishes with an exception,
# the exception is still raised and must be caught
with connection:
cursor.executemany("INSERT INTO user VALUES(:name, :age, :sex)", data)
except sqlite3.IntegrityError as error:
print(f"Error while creating the database, {error}")
# Will add again same data, will throw an error now!
with connection:
cursor.executemany("INSERT INTO user VALUES(:name, :age, :sex)", data)
except sqlite3.IntegrityError as error:
print(f"Error while creating the database, {error}")
cursor.execute("SELECT * FROM user WHERE age >= ?", (445, ))
users = cursor.fetchall()
for user in users:
print(user["name"], user["age"], user["sex"])
Output
Error while creating the database, UNIQUE constraint failed: user.name
kam 445 M
kam2 445 M
Documentatin
How to use the connection context manager
How to use placeholders to bind values in SQL queries
#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!
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.
