Working with databases in Python is easy with the vast support for so many databases including MySQL, Oracle, PostgreSQL, SQLite and so many others.
Python has a standard DB interface that enables you to connect, create or manipulate a database. In this article, we will be working with the sqlite3 and the same principles can be applied to other databases as well.
Here are steps to working with a database.
Connect with the database
In order to work with the sqlite database, you first must import the sqlite3 module. Using the connect() method, you can connect to an existing database or create one if the database does not exist.
import sqlite3
conn = sqlite3.connect('data')
Creating a table
Having connected to a database or created one, the next thing to do is to work with the tables already existing in the database or create new ones.
To do this, you have to be able to execute sql commands on the database. In this regard, you have to create a cursor object.
The cursor object lets you use the execute method to create and execute sql commands on the database. We will be using the cursor object to create a table store in the database and as well insert a record into the table.
cur = conn.cursor()
#perform sql operations
cur.execute('''create table store (item text, amount real, qty integer)''')
cur.execute('insert into store values ("soap",2.50, 1000)')
#Commit the changes
conn.commit()
#Close connection
conn.close()
Retrieving records from the database
conn = sqlite3.connect('data')
cur = conn.cursor()
output = cur.execute('select * from store')
for row in output:
print(row)
#outputs
# ('soap', 2.5, 1000)
Substituting values in queries using the ? operator
The question mark operator (?) enables you to perform substitutions in queries as shown below:
soft = ('soft drinks', 1.99, 111)
cur.execute("insert into store values (?, ?, ?)", soft)
items = cur.execute('select * from store')
#print records in the table
for row in items:
print(row)
#outputs
# ('soap', 2.5, 1000)
# ('soft drinks', 1.99, 111)
Bulk Inserts
You can insert bulk data into a table using the executemany() method of the cursor.
#perform bulk inserts
raw_data = [('detergent', 1.99, 100), ('lotion', 2.99, 140), ('powder', 1.00, 200), ('bulb', 4.99, 124)]
cur.executemany("insert into store values (?, ?, ?)", raw_data)
items = cur.execute('select * from store')
for row in items:
print(row)
#outputs
# ('soap', 2.5, 1000)
# ('detergent', 1.99, 100)
# ('lotion', 2.99, 140)
# ('powder', 1.0, 200)
# ('bulb', 4.99, 124)