Python sqlite inserting single and multiple records to table

SQLite SQLite insert SQLAlchemy

We are using our student table. Check how to create database and student table here.

INSERT Query to add single or multiple rows to table & get number of rows added with lastrowid

Adding single record / row

To our student table we will use SQL INSERT to add one row or record.

We have 5 columns in our table but we are using NULL as data for first column ( id ). As id column is an auto incremented id column , SQLite will add next highest number to this column while adding the row. If it is the first record then the ID value will be 1, for the next record added the ID value will be 2 and so on..

my_query="INSERT INTO student values(null,'New Name', 'Four', 65, 'female')"
my_conn.execute(my_query)

We can also read the ID ( alow known as rowid ) of most recently added row.

x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])

Output

Using parameter

Always use parameterized query when the data is coming from unknown sources. Use ? as placeholder and a provide a tuple to pass the value to query or execute() method. This is required to prevent injection attack.

We have used placeholders ( ? ) in our query and note that my_data is a tuple used for passing value to execute() method for our query.

my_data=(None,'Secon Name','Five',75,'male')
my_query="INSERT INTO student values(?,?,?,?,?)"
my_conn.execute(my_query,my_data)

We can check the ROWID of this added record. ( code with output as 3 )

x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0]) # 3

Adding multiple records / rows

More records can be added by using executemany() function.

my_data=[(9, 'Tes Qry', 'Six', 78, 'male'),
(10, 'Big John', 'Four', 55, 'female'),
(11, 'Ronald', 'Six', 89, 'female'),
(12, 'Recky', 'Six', 94, 'female'),
(13, 'Kty', 'Seven', 88, 'female')]

my_query="INSERT INTO student values(?,?,?,?,?)"
my_conn.executemany(my_query,my_data)

We can check the row id of the last inserted records by using last_insert_rowid().

x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])

Output is

Number of rows added

Number of records added by using rowcount

my_data=[(18, 'Big John', 'Four', 55, 'female'),
(19, 'Ronald', 'Six', 89, 'female'),
(20, 'ONe more', 'Six', 89, 'female')]
my_query="INSERT INTO student values(?,?,?,?,?)"
curs=my_conn.executemany(my_query,my_data)
print(curs.rowcount)

Output

Printing Error Message

By using try except we can print the error message if any and that will help in debugging the script.

try:
    my_data=[(24, 'Big John', 'Four', 55, 'female'),
        (25, 'Ronald', 'Six', 89, 'female'),
        (26, 'ONe more', 'Six', 89, 'female')]
    my_query="INSERT INTO student values(?,?,?,?,?)"
    curs=my_conn.executemany(my_query,my_data)
    print(curs.rowcount())
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output ( as we already added the records with the above IDs )

error:  UNIQUE constraint failed: student.id

Using SQLalchemy to insert record Add record to SQLite table using Tkinter GUI Sqlite Connection delete update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

SQLite INSERTING records

Adding single record / row

Using parameter

Adding multiple records / rows

Number of rows added

Printing Error Message

Subscribe