Write a Python program that connects to a sqlite database.
Create a table called Horses with the following fields:
- id (integer): a primary key and not null
- name (text)
- breed (text)
- height (real)
- birthday (text)
Next, insert the following data row into the Horses table:
id: 1 name: 'Babe' breed: 'Quarter Horse' height: 15.3 birthday: '2015-02-10'
Output all records from the Horses table.
Ex: With the above row inserted, the output should be:
All Horses: (1, 'Babe', 'Quarter Horse', 15.3, '2015-02-10')
import sqlite3
from sqlite3 import Error
def create_connection(db_file):
""" create a database connection to the SQLite database
specified by db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return conn
def create_table(conn, create_table_sql):
""" create a table from the create_table_sql statement
:param conn: Connection object
:param create_table_sql: a CREATE TABLE statement
:return:
"""
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
def create_task(conn, task):
"""
Create a new task
:param conn:
:param task:
:return:
"""
sql = ''' INSERT INTO Horses(id,name,breed,height,birthday)
VALUES(?,?,?,?,?) '''
cur = conn.cursor()
cur.execute(sql, task)
conn.commit()
return cur.lastrowid
def main():
database = "pythonsqlite.db"
sql_create_horse_table = """ CREATE TABLE IF NOT EXISTS Horses (
id integer PRIMARY KEY,
name text NOT NULL,
breed text,
height real,
birthday text
); """
# create a database connection
conn = create_connection(database)
# create tables
if conn is not None:
# create projects table
create_table(conn, sql_create_horse_table)
else:
print("Error! cannot create the database connection.")
print("Table created")
#You can create as many tasks you can
#we consider each task as insertion
task_1 = (1, 'Babe', 'Quarter Horse', 15.3, '2015-02-10')
create_task(conn, task_1)
print("Records Inserted")
if __name__ == '__main__':
main()