SQLite3 in Python
SQLite3 in Python: Python ships a module called sqlite3 which enables you to work with a builtin database called SQLite3 without having to install its client application. SQLite3 is compliant with DB-API 2.0 specification with an SQL interface.
SQLite3 in Python
In this article, I'll briefly go over how to
- connect to a fresh database / connect to an existing database
- execute DDL (Data Definition Language) statements like CREATE, ALTER
- execute DML (Data Manipulation Language) statements like INSERT, UPDATE, DELETE
- execute DQL (Data Query Language) statement i.e. SELECT
Connect to a Fresh Database / Connect to an Existing Database§
Use the connect() function to connect to an existing database (a .db file) in the current directory. If the specified file doesn't exist, it will create a new file in the current directory. This method returns a Connection object which we can use later to execute DDL, DML and other types of statements.
>>> help(sqlite3.connect)
Help on built-in function connect in module _sqlite3:
connect(...)
connect(database[, timeout, detect_types, isolation_level,
check_same_thread, factory, cached_statements, uri])
Opens a connection to the SQLite database file *database*. You can use
":memory:" to open a database connection to a database that resides in
RAM instead of on disk.
>>> sqlite3Connection = sqlite3.connect('myTest.db')
Executing DDL (Data Definition Language) statements: CREATE, ALTER§
The execute() function of the Connection object allows us to execute statements.
>>> sqlite3Connection = sqlite3.connect('myTestDB.db')
## DDL: CREATE ##
>>> sqlite3Connection.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL);''')
<sqlite3.Cursor object at 0x0368C1A0>
## DDL: ALTER ##
>>> sqlite3Connection.execute('ALTER TABLE EMPLOYEE ADD COLUMN DOB DATE;')
<sqlite3.Cursor object at 0x039051A0>
Executing DML (Data Manipulation Language) statements: INSERT, UPDATE, DELETE§
>>> sqlite3Connection = sqlite3.connect('myTestDB1.db')
>>> sqlite3Connection.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL);''')
<sqlite3.Cursor object at 0x03D26CE0>
## DML: INSERT ##
>>> sqlite3Connection.execute('''INSERT INTO EMPLOYEE
(EMP_ID, FIRST_NAME, LAST_NAME)
VALUES(1, 'Ethan', 'Hunt');''')
<sqlite3.Cursor object at 0x039051A0>
## DML: UPDATE ##
>>> sqlite3Connection.execute('''UPDATE EMPLOYEE
SET EMP_ID=1
WHERE FIRST_NAME = 'Ethan';''')
<sqlite3.Cursor object at 0x03D26D20>
## DML: DELETE ##
>>> sqlite3Connection.execute('''DELETE FROM EMPLOYEE
WHERE EMP_ID = 1;''')
<sqlite3.Cursor object at 0x03D26D60>
Executing DQL (Data Query Language) statement: SELECT§
>>> sqlite3Connection = sqlite3.connect('myTestDB1.db')
>>> sqlite3Connection.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL);''')
<sqlite3.Cursor object at 0x03D26CE0>
>>> sqlite3Connection.execute('''INSERT INTO EMPLOYEE(EMP_ID, FIRST_NAME, LAST_NAME) VALUES(007, 'Ethan', 'Hunt');''')
<sqlite3.Cursor object at 0x039051A0>
## DQL: SELECT ##
>>> cursor = sqlite3Connection.execute("SELECT * from EMPLOYEE")
>>> for record in cursor:
print(record)
(7, 'Ethan', 'Hunt')
That's it for this one! This article only scratches the surface of what the sqlite3 module has to offer. Having said that, I hope it will help you get started. Cheers!
See also:
- Python 101
- Object Oriented Python
- Design Patterns in Python
- 50+ Handy Standard Library Modules
- 60+ Handy Third Library Modules
- 50+ Tips & Tricks for Python Developers
- 50+ Know-How(s) Every Pythonista Must Know