Description
5.8 LAB – Database programming with Python (SQLite)
Complete the Python program to create a Horse table, insert one row, and display the row. The main program calls four functions:
create_connection()
creates a connection to the database.create_table()
creates the Horse table.insert_horse()
inserts one row into Horse.select_all_horses()
outputs all Horse rows.
Complete all four functions. Function parameters are described in the template. Do not modify the main program.
The Horse table should have five columns, with the following names, data types, constraints, and values:
NameData typeConstraintsValueIdintegerprimary key, not null1Nametext’Babe’Breedtext’Quarter horse’Heightdouble15.3BirthDatetext’2015-02-10′
The program output should be:
All horses:
(1, 'Babe', 'Quarter Horse', 15.3, '2015-02-10')
This lab uses the SQLite database rather than MySQL. The Python API for SQLite is similar to MySQL Connector/Python. Consequently, the API is as described in the text, with a few exceptions:
- Use the import library provided in the program template.
- Create a connection object with the function
sqlite3.connect(":memory:")
. - Use the character
?
instead of%s
as a placeholder for query parameters. - Use data type
text
instead ofchar
andvarchar
.
SQLite reference information can be found at SQLite Python Tutorial, but is not necessary to complete this lab.
this is what ai have so far and it is not completely right :
Import sqlite3
from sqlite3 import Error
#create connections to sqlite in memory database
def create_connections():
”’create a connection to in memory database return connection object”’
#use sqlite3.connect(“:memory:”) to create a connection object
conn = sqlite3.connect(“:memory:”)
return conn
#create horse table
def create_table(conn):
”’create horse table
:param conn: connection object
:return: Nothing
”’
#create a cursor object
cur = conn.cursor()
#execute a SQL statement to create a table
cur.execute(“CREATE TABLE horse (id INTEGER PRIMARY KEY, name TEXT, breed TEXT, height REAL, date_of_birth DATE)”)
#commit the changes
conn.commit()
#insert one row to horse tble given data tuple
def insert_horse(conn, horse):
”’insert one row to horse table
:param conn: connection object
:param horse: tuple of values for new row
:return: nothing
”’
#create a cursor object
cur = conn.cursor()
#execute a SQL statement to insert a row
cur.execute(“INSERT INTO horse VALUES (?,?,?,?,?)”, horse)
#commit the changes
conn.commit()
#select all rows from horse table
def select_all_horses(conn):
”’select all rows in the horse table
:param conn: connection object
:return: nothing
”’
#create a cursor object
cur = conn.cursor()
#execute a SQL statement to select all rows
cur.execute(“SELECT * FROM horse”)
#fetch all rows
rows = cur.fetchall()
#print all rows
for row in rows:
print(row)
if __name__==’__main__’:
#create connection to sqlite in memory database
conn = create_connections()
if conn is None:
print(“Error! cannot create the database connection.”)
#create horse table
create_table(conn)
#insert one row to horse table
horse = (1, ‘Babe’, ‘Quarter Horse’, 15.3, ‘2015-02-10’)
insert_horse(conn, horse)
#select all rows from horse table
print(“All horses:”)
select_all_horses(conn)