Python 3home |
The sqlite3 module provides programmatic access to sqlite3 databases.
Keep in mind that the interface you use for SQLite3 will be very similar to one that you would use for other databases such as mysql/MariaDB, so most of the code after the "connect()" call will be identical.
import sqlite3
conn = sqlite3.connect('example.db') # a db connection object
# generate a cursor object
c = conn.cursor() # a cursor object for issuing queries
# execute change to database
c.execute("INSERT INTO test.dbo.revenue (company, state, cost) VALUES ('Acme, Inc.', 'CA', 23.9)")
# commit change
conn.commit() # all changes to db must be committed
# select data from database
c.execute('SELECT * FROM test.dbo.revenue')
# cursor.description: database columns
desc = c.description
for field in desc:
fname = field[0]
ftype = field[1]
print(f'{fname}: {ftype}')
### id: <class 'int'>
### first_name: <class 'str'>
### last_name: <class 'str'>
### birthday: <class 'datetime.date'>
# fetching options (customarily only one of these will be used)
# loop through results
for row in c:
print(row)
# fetch one row
row = c.fetchone()
print(row)
# fetch several rows
rows = c.fetchmany(3)
print(rows)
# fetch all rows
rows = c.fetchall()
print(rows)
# close the connection
conn.close()
The connect string validates the user and specifies a host and database
Connecting to Database (and Disconnecting When Done)
The 'connect string' passed to connect() is the name of a database file. In other databases it would be a longer "connect string" that includes user and database information.
import sqlite3
conn = sqlite3.connect('example.db') # a db connection object
# after completing database actions, close the connection promptly
# conn.close()
Generating a cursor object for issuing queries
A cursor object represents a request to the database. We will use the cursor object to execute queries and retrieve query results.
import sqlite3
conn = sqlite3.connect('example.db') # a db connection object
c = conn.cursor()
# after completing database actions, close the connection promptly
# conn.close()
Four options for retrieving result rows: fetchone(), fetchmany(), fetchall() and 'for' looping
executing a select query
c.execute('SELECT * FROM revenue')
fetching options: depending on the size of the result set and how we'd like process the results, we have four options for retrieving results from the cursor object once a query has been executed.
looping through result set: similar to file looping
for row in c:
print(row) # returns a tuple row with each iteration
retrieving one row: most appropriate when only one row is expected
row = c.fetchone() # returns a single tuple row
fetching several rows: allows processing results in batches
rows = c.fetchmany(3) # returns a list of 3 tuple rows
fetching all rows: appropriate only if result set is not very large
rows = c.fetchall() # returns a list of tuple rows
cursor.description: database columns information
This attribute of the cursor object contains a tuple structure that describes each column: its name and type, as well as a number of internal attributes
desc = c.description
for field in desc:
fname = field[0]
ftype = field[1]
print(f'{fname}: {ftype}')
### id: <class 'int'>
### first_name: <class 'str'>
### last_name: <class 'str'>
### birthday: <class 'datetime.date'>
Any change to database must be committed to be made permanent.
c.execute("INSERT INTO revenue (company, state, cost) VALUES ('Acme, Inc.', 'CA', 23.9)")
conn.commit()
Inserting values into query dynamically
SQL requires quotes around text values and no quotes around numeric values. This can cause confusion or issues, especially when our text values have quotes in them.
sqlite3 can be made to add the quotes for us, thus escaping any values as needed:
name = "Joe's Fashions" # the single quote could be a problem when inserted into an SQL statement
state = 'NY'
value = 1.09
c.execute("INSERT INTO revenue (company, state, cost) VALUES (?, ?, ?)", (name, state, value))
conn.commit()
we can also insert multiple rows in one statement using this method:
insert_rows = [ ("Joe's Fashions", 'NY', 1.09), ('Beta Corp', 'CA', 1.39) ]
c.executemany("INSERT INTO test.dbo.revenue (company, state, cost) VALUES (?, ?, ?)", insert_rows)
conn.commit()
Delete some or all rows with this query (take care!)
DELETE FROM removes rows from a table.
DELETE FROM students WHERE student_id = 'jk43'
Take special care -- DELETE FROM with no critera will empty the table!
DELETE FROM students
WARNING -- the above statement clears out the table!
This exception is generated by SQLite3, usually when our query syntax is incorrect.
When you receive this error, it means you have asked sqlite3 to do something it is unable to do. In these cases, you should print the query just before the execute() statement.
query = "INSERT INTO revenue (company, state) VALUES ('Acme', 'CA')"
c.execute(query)
conn.commit()
Traceback (most recent call last):
File "", line 1, in
sqlite3.OperationalError: table revenue has 3 columns but 2 values were supplied
A common issue is the use of single quotes inside a quoted value:
query = "INSERT INTO revenue (compnay, state, cost) VALUES ('Awful's', 'NJ', 20.39)"
c.execute(query)
conn.commit() # all changes to db must be committed
Traceback (most recent call last):
File "", line 1, in
sqlite3.OperationalError: near "A": syntax error
Looking closely at the query above, you see that the name "Awful's" has a quote in it. So when SQL attempts to parse this string, it becomes confused by the quote in the text. The solution to this is to use parametarized arguments as described previously.