CODEWITHSUNDEEP
pythonsqllistsqliteinsert
webminal.org
webminal.org

Reputation: 47296

Python and SQLite: insert into table

Having the following table schema:

tablename(
    name varchar[100], 
    age int, 
    sex char[1]
)

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),)

Upvotes: 68

Views: 198185

Answers (8)

user22113446
user22113446

Reputation: 1

rowPosition = 0 for one_row in cursor:

        self.tableWidget.insertRow(rowPosition)
        self.tableWidget.setItem(rowPosition,0,QtWidgets.QTableWidgetItem(str(one_row[0])))
        self.tableWidget.setItem(rowPosition,1,QtWidgets.QTableWidgetItem(str(one_row[1])))
        self.tableWidget.setItem(rowPosition,2,QtWidgets.QTableWidgetItem(str(one_row[2])))
        self.tableWidget.setItem(rowPosition,3,QtWidgets.QTableWidgetItem(str(one_row[3])))
        rowPosition+=1

Upvotes: 0

Federico Baù
Federico Baù

Reputation: 7735

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
try:
    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
try:
    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!
try:
    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

Upvotes: 1

Alexander Novas
Alexander Novas

Reputation: 87

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()

Upvotes: 0

Rishabh Bhardwaj
Rishabh Bhardwaj

Reputation: 11

#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!

Upvotes: -5

stil
stil

Reputation: 5576

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.

Upvotes: 15

ninjasmith
ninjasmith

Reputation: 1784

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()

Upvotes: 99

Dyno Fu
Dyno Fu

Reputation: 9044

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)

Upvotes: 8

Dominic Rodger
Dominic Rodger

Reputation: 99841

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

Upvotes: 50

Related Questions