Accessing Databases

We will look into Python's built-in SQLite DB module - sqlite3

I use Mozilla's plug-in SQLite manager tool to view the files I created with python, If you want to download Mozilla:
https://www.mozilla.org/en-US/firefox/new/#download-fx


SQLite Manager:
https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/



The module to work with SQLLite in python: sqlite3

Following code will create and connect to a database, then execute a query against that database



import sqlite3

conn = sqlite3.connect("zoo.sqlite") #create connection to zoo.sqlite database, creates the database if it doesn't already exist

cursor = conn.cursor() #provides are cursor to the above connection (the means of executing the SQL queries)

cursor.execute("create table animal_count (name text, count integer)") #execute the create table query

cursor.execute("insert into animal_count(name, count) values('Elephant', 3)") #inset a row into the animal_count table

cursor.execute("insert into animal_count(name, count) values('Crocodile', 5)")

conn.commit() #commit changes to the database

conn.close() #close the connection


zoo.sqlite will be created in the current working directory a better approach to pass data to a query is a parameterized query:

cursor.execute("insert into animal_count(name, count) values(?, ?)", ('Tiger', 2))

this approach escapes any special characters and also prevents SQLInjection.


To fetch the records from database, assign the result of the SELECT execute statement to a variable, which will receive a list of tuples, each tuple representing a row of data




import sqlite3

conn = sqlite3.connect("zoo.sqlite")

cursor = conn.cursor()

result = cursor.execute("select * from animal_count")

print(result.fetchall())

conn.commit()

conn.close()


To loop through the data, use:



mport sqlite3

conn = sqlite3.connect("zoo.sqlite")

cursor = conn.cursor()

result = cursor.execute("select * from animal_count")

for row in result:
	print(row)

conn.commit()

conn.close()


Executemany:

As an alternative to execute, you can use executemany, which takes a list of rows as parameter:



import sqlite3

conn = sqlite3.connect("zoo.sqlite")

cursor = conn.cursor()

animals=[('Frog', 10), ('Snake', 5), ('Turtle', 11)]

cursor.executemany("insert into animal_count(name, count) values(?, ?)", animals)

result = cursor.execute("select * from animal_count")

for row in result:
	print(row)

conn.commit()

conn.close()