- imShakil / python-sqlite-cheat-sheet.md
- Drop An SQLite Table From A Python Program
- Output 1 — When there is no table with the specified name exists:
- Output 2- When the table existed and got deleted:
- Output 3 — When IF EXISTS clause is added:
- Example 2 – SQLite DROP TABLE with foreign key constraints:
- Output:
- Python SQLite — Drop Table
- Syntax
- Example
- Dropping a table using Python
- Example
- Python SQLite — Drop Table
- Syntax
- Example
- Dropping a Table Using Python
- Example
imShakil / python-sqlite-cheat-sheet.md
SQLite3 is a very easy to use database engine. It is self-contained, serverless, zero-configuration and transactional. It is very fast and lightweight, and the entire database is stored in a single disk file. It is used in a lot of applications as internal data storage. The Python Standard Library includes a module called «sqlite3» intended for working with this database. This module is a SQL interface compliant with the DB-API 2.0 specification.
Table of Contents
import random import sqlite3
Connect and Create Database
We use the function sqlite3.connect to connect to the database. We can use the argument :memory: to create a temporary DB in the RAM or pass the name of a file to open or create it.
# create database in memory # db = sqlite3.connect(':memory:') # create database into directory db = sqlite3.connect("./data/test.db") # get a cursor object cursor = db.cursor()
Creating ( CREATE ) and Deleting ( DROP ) Tables
In order to make any operation with the database we need to get a cursor object and pass the SQL statements to the cursor object to execute them. Finally it is necessary to commit the changes. We are going to create a users table with name, phone, email and password columns.
# DROP TABLE cursor.execute("""DROP TABLE IF EXISTS users""") # CREATE TABLE cursor.execute( """CREATE TABLE IF NOT EXISTS users( id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT unique, password TEXT )""" ) db.commit()
Inserting ( INSERT ) Data into the Database
To insert data we use the cursor to execute the query. If you need values from Python variables it is recommended to use the «?» placeholder. Never use string operations or concatenation to make your queries because is very insecure. In this example we are going to insert two users in the database, their information is stored in python variables.
name = 'Halim' phone = "01234567890" email = "halim@email.com" password = "ha1234" cursor.execute( """INSERT INTO users(name, phone, email, password) VALUES (. )""", (name, phone, email, password), ) db.commit()
The values of the Python variables are passed inside a tuple. Another way to do this is passing a dictionary using the :key name placeholder:
name = "Alim" phone = "01234567890" email = "alim@email.com" password = "al1234" cursor.execute( """INSERT INTO users(name, phone, email, password) VALUES (:name, :phone, :email, :password)""", < "name": name, "phone": phone, "email": email, "password": password, >, ) db.commit() # use list of users for inserting multiple user info users = [ ( "Name " + str(i), str(random.randint(10000000, 1000000000)), "name" + str(i) + "@email.com", str(random.randint(10000, 90000)), ) for i in range(10) ] cursor.executemany( """INSERT INTO users(name, phone, email, password) VALUES (?, ?, ?, ?)""", users ) db.commit()
If you need to get the id of the row you just inserted use lastrowid
print(f"last row id: cursor.lastrowid>")
Retrieving Data ( SELECT ) from Database
To retrieve data, execute the query against the cursor object and then use fetchone() to retrieve a single row or fetchall() to retrieve all the rows and fetchmany() to retrieve a particular number or rows. (note: retrieve rows fetched as a list where each row as a tuple)
cursor.execute("""SELECT name, phone, email FROM users""") user1 = cursor.fetchone() print(user1) user_many = cursor.fetchmany(5) print(user_many) user_all = cursor.fetchall() print(user_all)
The cursor object works as an iterator, invoking fetchall() automatically
cursor.execute("""SELECT name, email, phone FROM users""") for row in cursor: print(f"name: row[0]> email: row[1]> phone: row[2]>")
To retrieve data with conditions, use again the «?» placeholder
user_id = 5 cursor.execute("""SELECT name, email, phone FROM users WHERE (user_id,)) print(cursor.fetchone())
Updating ( UPDATE ) and Deleting ( DELETE ) Data
The procedure to update or delete data is the same as inserting data
# update user phone with >cursor.execute("""UPDATE users SET phone = ? WHERE ("01710567890", user_id)) db.commit() # delete user row with >cursor.execute("""DELETE FROM users WHERE (8,)) db.commit()
Using SQLite Transactions
Transactions are an useful property of the database systems. It ensures the atomicity of the Database. Use commit() method to save the changes and rollback() method to roll back any change to the database since the last call to commit.
# update user phone with >cursor.execute("""UPDATE users SET phone = ? WHERE ("01712567890", user_id)) db.rollback()
Please remember to always call commit to save the changes. If you close the connection using close or the connection to the file is lost (maybe the program finishes unexpectedly), not committed changes will be lost.
SQLite Database Exceptions
For best practices always surround the database operations with a try clause or a context manager.
try: # create or connect database db = sqlite3.connect("./data/test.db") # get a cursor object cursor = db.cursor() # check if a table 'users' does exist or not and create it cursor.execute( """CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT unique, password TEXT)""" ) # commit to save the changes db.commit() except Exception as e: # rollback any change if something goes wrong db.rollback() raise e finally: db.close()
We can use the Connection object as context manager to automatically commit or rollback transactions
name1 = "Mobarak" phone1 = "3366858" email1 = "imshakil@github.com" # A very secure password password1 = "12345" try: db = sqlite3.connect("./data/test.db") with db: db.execute( """INSERT INTO users (name, phone, email, password) VALUES (?, ?, ?, ?)""", (name1, phone1, email1, password1), ) except sqlite3.IntegrityError: print("Data already exists") finally: db.close()
In the example above if the insert statement raises an exception, the transaction will be rolled back and the message gets printed; otherwise the transaction will be committed. Please note that we call execute on the db object, not the cursor object.
SQLite Row Factory and Data Types
The following table shows the relation between SQLite datatypes and Python datatypes:
- None type is converted to NULL
- int type is converted to INTEGER
- float type is converted to REAL
- str type is converted to TEXT
- bytes type is converted to BLOB
The row factory class sqlite3.Row is used to access the columns of a query by name instead of by index.
db = sqlite3.connect("./data/test.db") db.row_factory = sqlite3.Row cursor = db.cursor() cursor.execute("""SELECT name, email, phone FROM users""") for row in cursor: print(f"name : row[0]>, email: row[1]>, phone: row[2]>") # close database connection db.close()
Drop An SQLite Table From A Python Program
Output 1 — When there is no table with the specified name exists:
Traceback (most recent call last):
File «drop_sqlite1.py», line 14, in
sqlite3.OperationalError: no such table: demotable
Output 2- When the table existed and got deleted:
The output below does not include demotable as it was deleted.
[(‘table’, ‘test’, ‘test’, 2, ‘CREATE TABLE test(id int)’)]Output 3 — When IF EXISTS clause is added:
dropTableStatement = «DROP TABLE demotable»
When the example Python Program is modified to have the DROP TABLE statement as given below to include an IF EXISTS clause SQLite does not throw any Error and the output will be the same as output 2.
Example 2 – SQLite DROP TABLE with foreign key constraints:
DROP TABLE command in turn deletes the rows present in the table, using DELETE FROM command. However when such an implicit DELETE FROM is initiated if it violates any foreign key constraints SQLite will throw an error and will not delete the rows and DROP TABLE will fail, forcing the integrity of the database.
Assume there are two tables named
in an SQLite Database and Course table is defined with a foreign key definition to the Teacher Table.
TeacherId INTEGER PRIMARY KEY,
FOREIGN KEY(Teacher) REFERENCES Teacher(TeacherId)
The DROP TABLE will fail if deleting any Teacher record renders a course invalid.
# ——— Example Python Program for dropping an SQLite Table———
# import the sqlite3 module
# Connect to the demo database
# Enable Foreign key constraints
cursor.execute(«PRAGMA foreign_keys = ON;»)
# Execute the DROP Table SQL statement
dropTableStatement = «DROP TABLE Teacher»
# Close the connection object
Output:
Traceback (most recent call last):
File «drop_sqlite2.py», line 17, in
sqlite3.IntegrityError: FOREIGN KEY constraint failed
Python SQLite — Drop Table
You can remove an entire table using the DROP TABLE statement. You just need to specify the name of the table you need to delete.
Syntax
Following is the syntax of the DROP TABLE statement in PostgreSQL −
Example
Assume we have created two tables with name CRICKETERS and EMPLOYEES using the following queries −
sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); sqlite>
Now if you verify the list of tables using the .tables command, you can see the above created tables in it ( list) as −
sqlite> .tables CRICKETERS EMPLOYEE sqlite>
Following statement deletes the table named Employee from the database −
sqlite> DROP table employee; sqlite>
Since you have deleted the Employee table, if you retrieve the list of tables again, you can observe only one table in it.
sqlite> .tables CRICKETERS sqlite>
If you try to delete the Employee table again, since you have already deleted it you will get an error saying “no such table” as shown below −
sqlite> DROP table employee; Error: no such table: employee sqlite>
To resolve this, you can use the IF EXISTS clause along with the DELTE statement. This removes the table if it exists else skips the DLETE operation.
sqlite> DROP table IF EXISTS employee; sqlite>
Dropping a table using Python
You can drop a table whenever you need to, using the DROP statement of MYSQL, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table.
Example
To drop a table from a SQLite3 database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it.
import sqlite3 #Connecting to sqlite conn = sqlite3.connect('example.db') #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists cursor.execute("DROP TABLE emp") print("Table dropped. ") #Commit your changes in the database conn.commit() #Closing the connection conn.close()
Python SQLite — Drop Table
You can remove an entire table using the DROP TABLE statement. You just need to specify the name of the table you need to delete.
Syntax
Following is the syntax of the DROP TABLE statement in PostgreSQL −
Example
Assume we have created two tables with name CRICKETERS and EMPLOYEES using the following queries −
sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); sqlite>
Now if you verify the list of tables using the .tables command, you can see the above created tables in it (list) as −
sqlite> .tables CRICKETERS EMPLOYEE sqlite>
Following statement deletes the table named Employee from the database −
sqlite> DROP table employee; sqlite>
Since you have deleted the Employee table, if you retrieve the list of tables again, you can observe only one table in it.
sqlite> .tables CRICKETERS sqlite>
If you try to delete the Employee table again, since you have already deleted it you will get an error saying “no such table” as shown below −
sqlite> DROP table employee; Error: no such table: employee sqlite>
To resolve this, you can use the IF EXISTS clause along with the DELETE statement. This removes the table if it exists else skips the DELETE operation.
sqlite> DROP table IF EXISTS employee; sqlite>
Dropping a Table Using Python
You can drop a table whenever you need to, using the DROP statement of MYSQL, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table.
Example
To drop a table from a SQLite3 database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it.
import sqlite3 #Connecting to sqlite conn = sqlite3.connect('example.db') #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists cursor.execute("DROP TABLE emp") print("Table dropped. ") #Commit your changes in the database conn.commit() #Closing the connection conn.close()