SQLite INSERTING records

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
2
Or
r_set=my_conn.execute(q,my_data)
print(r_set.lastrowid)

Using parameter


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
13

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
3

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 Blob Data
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    02-02-2023

    Thank you for this article. It offers more than all the other websites I have seen so far.

    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2023 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer