Everything we did in the last articles of the series SQL-Tutorial was a dry run because we just used SQLFiddle.
So let’s start with a real database like SQLite.
SQLite is a file based DBRMS and can be used for e.g. web sites. The official docs say:
“SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). [..] Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.”
Because Knight Industries is not Google, Amazon nor Facebook we can definitely use SQLite.
Creating and connecting to a database
In Python it is pretty easy to connect to a SQLite database:
from sqlite3 import connect
db_connection = connect('knight_industries.db')
If the file knight_industries.db does not exist, it will be created automagically. A nice little feature of the sqlite3 library.
But be careful: If You already have a database file and you mess up the path in the connect statement you will wonder why you cannot access your data, because a new file is created silently.
cursor = db_connection.cursor()
cursor.execute('''CREATE TABLE operatives (id INTEGER, name TEXT, birthday DATE)''')
cursor.execute('''INSERT INTO operatives (id, name, birthday) \
VALUES (1, "Michael Arthur Long", "1949-01-09")''')
db_connection.commit()
cursor.execute('''SELECT * FROM operatives''')
print cursor.fetchone()
db_connection.close()
