SQLite INSERTING records

SQLite

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

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

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
Add record to SQLite table using Tkinter GUI Sqlite Connection delete update select Order By


plus2net.com



Post your comments , suggestion , error , requirements etc here




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