Python and SQLite: insert into table

Question

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

Its fixed now. I have used this wrong method self.cursor.execute("insert into server(server,c_id,status) values (?,?,?)",(t[0],),(t[1],),(t[2],)) right method is self.cursor.execute("insert into server(server,c_id,status) values (?,?,?)",(t[0],t[1],t[2],)) — webminal.org
– webminal.org, Commented Jan 25, 2010 at 6:49

Kamen Tsvetkov · Accepted Answer · 2016-02-10 00:32:23Z

99

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

edited Feb 10, 2016 at 0:32

Kamen Tsvetkov

5895 silver badges9 bronze badges

answered May 16, 2011 at 15:35

ninjasmith

1,7841 gold badge13 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mixstah Over a year ago

I have a query about this because I have tried it but I get and error saying I have 6 columns and only provided information for 5. This is true but the 1st column is pk integer and you don't need to provide it with a value. Is there a way to over come this? –

Mixstah Over a year ago

Figured it out. Just add the field headers after the db name as you would normally.

Wolfgang Fahl Over a year ago

Thx i used an approach based on this anwer in the wrapper github.com/WolfgangFahl/DgraphAndWeaviateTest/blob/master/…

tanius Over a year ago

@WolfgangFahl's wrapper now can be found here.

Wolfgang Fahl Over a year ago

@tanius thx for tixing my comment - in fact there is now a library pyLoDStorage that has the code and can be integrated via pypi github.com/WolfgangFahl/pyLoDStorage/blob/master/lodstorage/…

Dominic Rodger · Accepted Answer · 2010-01-19 10:36:03Z

50

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

answered Jan 19, 2010 at 10:36

Dominic Rodger

100k37 gold badges204 silver badges219 bronze badges

7 Comments

webminal.org Over a year ago

Thanks Dyno and Dominic - But it's not working - this is what i'm trying ----------- for record in list: print "--->",record cursor.execute("insert into process values (?,?,?)",record); ----------- getting error

webminal.org Over a year ago

I have try, except part - try part will have for loop and execute stmt and except part has single message saying "insert failed" ...it just prints the message from except part and quits. How to debug this more effectively ?

Ignacio Vazquez-Abrams Over a year ago

@lakshmipathi: If your exception handling is chewing up the error message then it isn't really handling it...

webminal.org Over a year ago

how to print error number .. atleast that would provide some hint

iamabhaykmr Over a year ago

It wont' insert unless we use conn.commit()

|

stil · Accepted Answer · 2016-04-17 15:17:57Z

15

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.

answered Apr 17, 2016 at 15:17

stil

5,5963 gold badges40 silver badges47 bronze badges

1 Comment

bl79 Over a year ago

What means ':' in vals before fieldnames? And how to validate the names?

Peter Mortensen · Accepted Answer · 2010-02-09 19:10:11Z

8

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)

edited Feb 9, 2010 at 19:10

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jan 19, 2010 at 10:34

Dyno Fu

9,0744 gold badges46 silver badges73 bronze badges

Comments

Federico Baù · Accepted Answer · 2023-04-29 18:12:00Z

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

Alexander Novas · Accepted Answer · 2019-09-05 20:31:50Z

0

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

answered Sep 5, 2019 at 20:31

Alexander Novas

871 silver badge3 bronze badges

Comments

user22113446 · Accepted Answer · 2023-06-22 08:59:34Z

0

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

answered Jun 22, 2023 at 8:59

user22113446

1

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Rishabh Bhardwaj · Accepted Answer · 2019-06-22 21:11:58Z

-5

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

edited Jun 22, 2019 at 21:11

answered Jun 22, 2019 at 20:39

Rishabh Bhardwaj

112 bronze badges

1 Comment

Ashley Ross Over a year ago

Please note that this approach is vulnerable to SQL injection attacks.

Collectives™ on Stack Overflow

Python and SQLite: insert into table

8 Answers 8

5 Comments

7 Comments

1 Comment

Comments

1) Using Dictionary instead of list

2) Use a context manager to automatically commit - rollback

Documentatin

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

5 Comments

7 Comments

1 Comment

Comments

1) Using Dictionary instead of list

2) Use a context manager to automatically commit - rollback

Documentatin

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related