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: 

1. create_connection() creates a connection to the database.
2. create_table() creates the Horse table.
3. insert_horse() inserts one row into Horse.
4. 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 of char and varchar.

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)