Python and sqlite Basics!

In this post I want to show some basic examples in the usage of Python module sqlite3 to use a simple database with Python. This is very easy to use and I am going to show how to:

• create a database (connect)
• create a table (execute… CREATE)
• insert data into the table (execute… INSERT)
• retrieve the data (fetchone and fetchall)

Think to the structure of the database like this:

• database (table container)
  • table (data structure –  data container)
    • data

So, if you want to create a database, you gotta connect trough the module like this:

import sqlite3 as lite


conn = lite.connect("MyDatabase2.db")

You will see in the folder a new file called like you named it above.

Then you gonna need the cursor method, before executing any operation on the database.

cur = conn.cursor()

Now you are able to execute the sql code to create table, insert and retrieve data.

Create a table

It’s simple like this:

cur.execute("""
CREATE TABLE book(
name text,
author text,
price real)
""")

We created the database structure, defining the type of data we want. The name, author and price are the name of the column of the database (think to an Excel data sheet, these would be the letters that are the columns). The text and real keywords are the type of data that the database expect to receive when you will insert the data.

Insert the data

Now it’s time for the real stuff: data. Here is how you can do it. We store 2 books into the table of the database.

cur.execute("INSERT INTO book VALUES('Learn Python', 'PythonProgramming.altervista.org', 100)")
cur.execute("INSERT INTO book VALUES('Python and Sqlite', 'PythonProgramming.altervista.org', 20)")

I inserted 2 book…

Retrieve the data

It’s time to look into the database for our data.

cur.execute("SELECT * FROM book WHERE name=?", ("Learn Python",))
print(cur.fetchall())

In this example we looked for the book called “Learn Python”. This is the output

[('Learn Python', 'PythonProgramming.altervista.org', 100.0)]

If we use this

print(cur.fetchone())

we will have this output

('Learn Python', 'PythonProgramming.altervista.org', 100.0)

Fetchall gives you all the matches in database. Fetchone gives you the matches one by one, like an iterator.

At the end you will commit your changes in the database and close it

conn.commit()

conn.close()

 

The whole code

import sqlite3 as lite


conn = lite.connect("MyDatabase2.db")

cur = conn.cursor()

cur.execute("""
CREATE TABLE book(
name text,
author text,
price real)
""")


cur.execute("INSERT INTO book VALUES('Learn Python', 'PythonProgramming.altervista.org', 100)")
cur.execute("INSERT INTO book VALUES('Python and Sqlite', 'PythonProgramming.altervista.org', 20)")

cur.execute("SELECT * FROM book WHERE name=?", ("Learn Python",))
print(cur.fetchall())


conn.commit()

conn.close()

The live coding video about sqlite and Python