These examples are taken from the Software Carpentry lesson for Databases and SQL, specifically the section on working with databases in Python.
To run these examples, download the survey.db SQLite database file and place it in the same folder as this notebook.
import sqlite3
connection = sqlite3.connect("survey.db")
cursor = connection.cursor()
cursor.execute("SELECT Site.lat, Site.long FROM Site;")
results = cursor.fetchall()
for r in results:
print(r)
cursor.close()
connection.close()
(-49.85, -128.57) (-47.15, -126.72) (-48.87, -123.4)
import sqlite3
def get_name(database_file, person_id):
query = "SELECT personal || ' ' || family FROM Person WHERE id='" + person_id + "';"
connection = sqlite3.connect(database_file)
cursor = connection.cursor()
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
connection.close()
return results[0][0]
print("Full name for dyer:", get_name('survey.db', 'dyer'))
Full name for dyer: William Dyer
import sqlite3
def get_name(database_file, person_id):
query = "SELECT personal || ' ' || family FROM Person WHERE id=?;"
connection = sqlite3.connect(database_file)
cursor = connection.cursor()
cursor.execute(query, [person_id])
results = cursor.fetchall()
cursor.close()
connection.close()
return results[0][0]
print("Full name for dyer:", get_name('survey.db', 'dyer'))
Full name for dyer: William Dyer
import sqlite3
def add_name(database_file, new_person):
query = "INSERT INTO Person (id, personal, family) VALUES (?, ?, ?);"
connection = sqlite3.connect(database_file)
cursor = connection.cursor()
cursor.execute(query, list(new_person))
cursor.close()
connection.close()
def get_name(database_file, person_id):
query = "SELECT personal || ' ' || family FROM Person WHERE id=?;"
connection = sqlite3.connect(database_file)
cursor = connection.cursor()
cursor.execute(query, [person_id])
results = cursor.fetchall()
cursor.close()
connection.close()
return results[0][0]
# Insert a new name
add_name('survey.db', ('barrett', 'Mary', 'Barrett'))
# Check it exists
print("Full name for barrett:", get_name('survey.db', 'barrett'))
--------------------------------------------------------------------------- IndexError Traceback (most recent call last) <ipython-input-4-2481a760cc61> in <module> 26 add_name('survey.db', ('barrett', 'Mary', 'Barrett')) 27 # Check it exists ---> 28 print("Full name for barrett:", get_name('survey.db', 'barrett')) <ipython-input-4-2481a760cc61> in get_name(database_file, person_id) 21 connection.close() 22 ---> 23 return results[0][0] 24 25 # Insert a new name IndexError: list index out of range
import sqlite3
def add_name(database_file, new_person):
query = "INSERT INTO Person (id, personal, family) VALUES (?, ?, ?);"
connection = sqlite3.connect(database_file)
cursor = connection.cursor()
cursor.execute(query, list(new_person))
cursor.close()
connection.commit()
connection.close()
def get_name(database_file, person_id):
query = "SELECT personal || ' ' || family FROM Person WHERE id=?;"
connection = sqlite3.connect(database_file)
cursor = connection.cursor()
cursor.execute(query, [person_id])
results = cursor.fetchall()
cursor.close()
connection.close()
return results[0][0]
# Insert a new name
add_name('survey.db', ('barrett', 'Mary', 'Barrett'))
# Check it exists
print("Full name for barrett:", get_name('survey.db', 'barrett'))
Full name for barrett: Mary Barrett
def remove_name(database_file, person_id):
query = 'DELETE from Person where id=?;'
connection = sqlite3.connect(database_file)
cursor = connection.cursor()
cursor.execute(query, [person_id])
cursor.close()
connection.commit()
connection.close()
# remove the name we added to put the database back into its initial state
remove_name('survey.db', 'barrett')