Question

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: name data type constraints value id integer primary key, not null 1 name text 'babe' breed text 'quarter horse' height double 15.3 birthdate text '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.

Answer 1

Below is the completed Python program with the four required functions for creating a horse table, inserting a row, and displaying the row.

import sqlite3

def create_connection():

# Create a connection to the in-memory SQLite database

return sqlite3.connect(":memory:")

def create_table(conn):

# Create the horse table with specified columns and constraints

try:

cursor = conn.cursor()

cursor.execute('''

CREATE TABLE horse (

id INTEGER PRIMARY KEY NOT NULL,

name TEXT,

breed TEXT,

height DOUBLE,

birthdate TEXT

)

''')

conn.commit()

except sqlite3.Error as e:

print(f"Error creating table: {e}")

def insert_horse(conn, id, name, breed, height, birthdate):

# Insert a row into the horse table

try:

cursor = conn.cursor()

cursor.execute('''

INSERT INTO horse (id, name, breed, height, birthdate)

VALUES (?, ?, ?, ?, ?)

''', (id, name, breed, height, birthdate))

conn.commit()

except sqlite3.Error as e:

print(f"Error inserting horse: {e}")

def select_all_horses(conn):

# Output all rows from the horse table

try:

cursor = conn.cursor()

cursor.execute('SELECT * FROM horse')

rows = cursor.fetchall()

print("all horses:", rows)

except sqlite3.Error as e:

print(f"Error selecting horses: {e}")

# Main program

if __name__ == '__main__':

# Create a connection to the database

connection = create_connection()

# Create the horse table

create_table(connection)

# Insert one row into the horse table

insert_horse(connection, 1, 'babe', 'quarter horse', 15.3, '2015-02-10')

# Display all rows from the horse table

select_all_horses(connection)

# Close the connection

connection.close()