SQLite3 – using a database in python

In this section we will talk about how to use a database in python, for both the use in graphic interfaces or terminal scripts. As a database system we will be using sqlite, which is included in the anaconda package. To be able to import the database system we type:

import sqlite3 as lite

This gives us the ability to connect to a database, which, if it doesn’t exist, is automatically created, when using Spyder from the Anaconda package, the database is created in the project or file directory. For this connection link we open a with loop, closing the connection when the last execution is finished, leaving no open database connection, blocking access or slowing it down. Using the connection to the database, we create a cursor (line 3), which we will use on execution (line 4). Finally we need to commit (push, post, etc.) the execute command to the database (line 5).

con = lite.connect('wordpress.db')
with conn:
  cur = con.cursor()
  cur.execute("CREATE TABLE IF NOT EXISTS blog (entry text, date text)")
  con.commit()

Creating a table in SQLite is a little different to for example phpmyadmin, as there is no graphical interface. Also the types are a little different, there are only four types available: INTEGER, REAL, TEXT and BLOB. Integer is as usual just a signed int (int, bigint), real is a floating point value such as double or float, text is a basic text string, such as char, varchar or text, and blob is no data-type, it stores exactly what it gets input, no formatting done.

con = lite.connect('wordpress.db')
with conn:
 cur = con.cursor()
 cur.execute("INSERT INTO blog VALUES ('This is great', '14.06.2017)')
 con.commit()

After inserting some test values as seen above, we want to fetch them from the database and print them into our terminal. To do that we save our results into a nested list (line 5 below) using cur.fetchall().

con = lite.connect('wordpress.db')
with conn:
 cur = con.cursor()
 cur.execute("SELECT * FROM blog")
 results = cur.fetchall()
 print(results)
 con.commit()

If you want to get separate values you can parse through results like a list, getting the number of results is always done by len(results). If there is only one value you can use cur.fetchone() instead of cur.fetchall(). In the cur.execute() every SQL command can be used, and variables added as follows:

entry = "This is a variable input"
cur.execute("INSERT INTO blog VALUES ('%s', '14.06.2017')" %entry)

Hope this get’s you started with your local database!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s