Python and SQLite - Inserting Data
In order to insert data into an SQLite database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.
Inserting data works in a similar fashion as selecting data using parameters. Firstly, a check is made to see if the database file actually exists. If it doesn’t, a message is displayed and execution of the program is halted. If successfully found, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the SQL statement when it is executed and a confirmation message is displayed. The execution of the SQL statement is wrapped in a ‘try-except-finally’ block to catch any errors that may arise and close the database connection at the end.
import os.path import sqlite3 # Database. database = 'testDB.db' connect = None # Check if database file exists. if not os.path.isfile(database): # Confirm incorrect database location and stop program execution. print("Error locating database.") quit() try: # Connect to database. connect = sqlite3.connect(database) except sqlite3.DatabaseError as e: # Confirm unsuccessful connection and quit. print("Database connection unsuccessful.") quit() # Cursor to execute query. cursor = connect.cursor() # Query parameters. firstname = "Fiona" lastname = "Jones" title = "Miss" dob = "1985-05-19" # SQL to insert data into the person table. sqlInsert = \ "INSERT INTO person (firstname, lastname, title, dob) \ VALUES (?, ?, ?, ?)" try: # Execute query and commit changes. cursor.execute(sqlInsert, (firstname, lastname, title, dob)) connect.commit() # Confirm successful addition of person information. print("Person information added successfully.") except sqlite3.DatabaseError as e: # Confirm error adding person information and stop program execution. print("Error adding person information.") quit() finally: # Close database connection. connect.close()
The contents of the ‘person’ table now looks as follows.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
2 | George | Jones | Mr | 1997-12-15 |
3 | Fred | Bloggs | Mr | 1975-05-07 |
4 | Alan | White | Mr | 1989-03-20 |
5 | Fiona | Jones | Miss | 1985-05-19 |