To create a new database, we first import sqlite3 and then instantiate a new database object with the sqlite3.connect() method.



In [1]:

    
import sqlite3
db = sqlite3.connect("name_database.db")

Next, we connect to the database with the sqlite3.connect() method and create a connection object called conn. Then, from the connection object conn, we create a cursor object called cur. The cursor object executes the database commands. The commands the cursor object cur executes are written in a database query language. Learning database query language is sort of like learning a whole new programming language. I am still note really familiar with the database language query commands or syntax. Before we can add records to the database, we need to create a table in the database.



In [2]:

    
# create a database called name_database.db
# add one table to the database called names_table
# add columns to the database table: Id, first_name, last_name, age 
conn = sqlite3.connect('name_database.db')
cur = conn.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS names_table (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name text,
    last_name text,
    age integer
    )""")
conn.commit()

cur.close()
conn.close()
db.close()

Now to add a new record to the database, we need to:

connect to the database, creating a connection object conn
create a cursor object cur based on the connection object
execute commands on the cursor object cur to add a new record to the database
commit the changes to the connection object conn
close the cursor object
close the connection object



In [3]:

    
conn = sqlite3.connect('name_database.db')
cur = conn.cursor()
cur.execute("INSERT INTO names_table VALUES(:Id, :first_name, :last_name, :age)",
              {'Id': None,
               'first_name': 'Gabriella',
               'last_name': 'Louise',
               'age': int(8)
              })
conn.commit()
cur.close()
conn.close()

Now let's see if we can retrieve the record we just added to the database.



In [4]:

    
conn = sqlite3.connect('name_database.db')
cur = conn.cursor()
cur.execute("SELECT first_name, last_name, age, MAX(rowid) FROM names_table")
record = cur.fetchone()
print(record)
cur.close()
conn.close()









    



('Gabriella', 'Louise', 8, 1)

Let's add another record to the database



In [5]:

    
conn = sqlite3.connect('name_database.db')
cur = conn.cursor()
cur.execute("INSERT INTO names_table VALUES(:Id, :first_name, :last_name, :age)",
              {'Id': None,
               'first_name': 'Maelle',
               'last_name': 'Levin',
               'age': int(5)
              })
conn.commit()
cur.close()
conn.close()

And again let's see the most recent record:



In [6]:

    
conn = sqlite3.connect('name_database.db')
cur = conn.cursor()
cur.execute("SELECT first_name, last_name, age, MAX(rowid) FROM names_table")
record = cur.fetchone()
print(record)
cur.close()
conn.close()









    



('Maelle', 'Levin', 5, 2)



In [ ]: