Posted on

SQLite3: Python and SQL

Everything we did in last articles 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()
Posted on

SQL-LIKE and wildcards

Se_en represents a pattern with a wildcard character. The _ means you can substitute any individual character here without breaking the pattern. The names Seven and Se7en both match this pattern.

The percentage sign % is another wildcard character that can be used with LIKE.

Posted on

SQL-Basics: Relations

As the Junior Data Scientist of Knight Industries we created a table to keep track of all our operatives: SQL-Basics: Create, Read, Update, Delete

Devon asks us to keep track of our operations aka missions as well. For the first implementation let us assume that a mission has one operative and an operative can participate in multiple missions. that’s what we call an 1 to many relationship. Continue reading “SQL-Basics: Relations”

Posted on

SQL-Basics: Create – Read – Update – Delete

 

This episode is about the basic statements needed to create, read, update and delete data in a database system.

Let’s assume we work as a data scientist for Knight Industries.  We want to help the Foundation of Law and Government to keep track of our operatives.

We decide to use a classic relational database management system or RDBMS. In order to explore Database Management Systems we can either install one locally or we can use an online tool like SQLFiddle.

To interact with RDBMS we use SQL – the Structured Query Language.

As the name says SQL (speak either S-Q-L or Sequel) is used to write structured queries. Think of “conversations” when You think of “queries”.

So, let’s fire up SQLFiddle. Continue reading “SQL-Basics: Create – Read – Update – Delete”