Accessing raw DB

You have already seen some SQL commands. Let's see how we can apply them in Python. First thing first, we need to import the sqlite3 library. Note that this library is preinstalled in Python.

Connection and cursor

First, to work with a database, you need to create a Connection object that represents the database:

conn = sqlite3.connect("myfirstrawdatabase.db") 

If there is no DB with such a name, it will be generated. You can create a database in RAM if you want to delete it when the program stops. To do it, pass :memory: instead of a DB's name to the connect() method.

After creating a connection, we need to create a cursor. It can be viewed as a pointer to one row in a set of rows. Most of the tasks with DB are performed with it. For example, let's see how to create a table:

c = conn.cursor()
c.execute(
    """ CREATE TABLE pets (
    name text,
    species text,
    age integer
)
"""
)
conn.commit()

As you can see, SQL commands in Python consist of SQLite with wrappings; you can pass update or delete statements directly to the .execute() method. The execute() method performs one SQL command at a time. If you want to execute several commands, use executescript(). You may notice that the command is written in six quotes instead of two. This is the so-called docstring that allows writing multiple lines of code inside it. For example, if you try to change 6 quotes to 2, you will get an error. The commit()method saves all your changes and applies them to a DB. You can write it after every time you execute a command. Once we've applied all our changes to the DB, we can close the connection to it.

conn.close()

Inserting multiple entries at a time can be done in this way:

some_pets = [("Bob", "dog", 3), ("Leo", "cat", 2), ("Captain", "parrot", 1)]
c.executemany("INSERT into pets VALUES (?, ?, ?)" , some_pets)

SQL commands are executed from the string, so we need to pass variables to it to simplify input. It can be done via question mark placeholders and a list of tuples, as shown in the example. Executemany() iterates over our list and places its contents in the corresponding rows.

Retrieving data from DB

For example, let's see how to print DB contents. We already have a table named pets in myfirstrawdatabase:

for row in c.execute("SELECT * FROM pets"):
    print(row)

#This will return:
#('Bob', 'dog', 3)
#('Leo', 'cat', 2)
#('Captain', 'parrot', 1)

As you can see, after executing a SELECT statement, we can use the cursor as an iterator. Also, we can retrieve data using fetchone() or fetchall() methods. The first one returns a tuple, the second one returns a list of tuples.

For example, the following code snippet will return the same result:

c.execute("SELECT * FROM pets")
for row in c.fetchall():
    print(row)

#This will return:
#('Bob', 'dog', 3)
#('Leo', 'cat', 2)
#('Captain', 'parrot', 1)

The primary key (PK) is a field that uniquely defines an entry. In sqlite3, PK is created automatically, but in other DBs, we must create it manually. To display entries with primary keys, we need to pass rowid, in addition to other arguments to the SELECT statement. For example:

c.execute("SELECT rowid,* FROM pets")
for record in c.fetchall():
    print(record)

#This will return:
#(1, 'Bob', 'dog', 3)
#(2, 'Leo', 'cat', 2)
#(3, 'Captain', 'parrot', 1)

As we can see, our first column is the PK of each entry.

Filtering

Let's discuss how we can retrieve data from a DB. Suppose you have a large DB, so selecting and retrieving all data, of course, is not an option. It would be great to select only certain entries from the DB. It can be done with one magic word: WHERE. Let's make some more entries in our pets table and select all entries containing parrots:

some_pets = [("Max", "dog", 5), ("Kitty", "cat", 5), ("Ozzy", "parrot", 1)]
c.executemany("INSERT into pets VALUES (?, ?, ?)" , some_pets)

c.execute("SELECT * FROM pets WHERE species = 'parrot' ")
for record in c.fetchall():
    print(record)

#This will return:
#('Captain', 'parrot', 1)
#('Ozzy', 'parrot', 1)

Also, we can specify a WHERE condition by adding AND, OR. For example:

c.execute("SELECT * FROM pets WHERE breed = 'parrot' AND name = 'Captain' ")
for record in c.fetchall():
    print(record)

#This will return:
#('Captain', 'parrot', 1)

Output ordering

Ordering of the output is done with the ORDER BY keywords. After ORDER BY, we put the name of the column by which we want to order our output and then, optionally, specify it — ascending or descending order via ASC and DESC, correspondingly.

c.execute("SELECT rowid, * FROM pets ORDER BY rowid DESC")
for record in c.fetchall():
    print(record)

#This will return:
#(6, 'Ozzy', 'parrot', 1)
#(5, 'Kitty', 'cat', 5)
#(4, 'Max', 'dog', 5)
#(3, 'Captain', 'parrot', 1)
#(2, 'Leo', 'cat', 2)
#(1, 'Bob', 'dog', 3)

There is also a prebuilt text ordering in alphabetical order:

c.execute("SELECT rowid, * FROM pets ORDER BY name ASC")
for record in c.fetchall():
    print(record)

#This will return:
#(1, 'Bob', 'dog', 3)
#(3, 'Captain', 'parrot', 1)
#(5, 'Kitty', 'cat', 5)
#(2, 'Leo', 'cat', 2)
#(4, 'Max', 'dog', 5)
#(6, 'Ozzy', 'parrot', 1)

Variables

Using variables in raw querying is easy. It is prohibited to use Python string operations to assemble string operations due to SQL Injection attacks. SQL injection is an attack that is performed through editing SQL commands by passing some code to variables:

#Never do this -- insecure!
var = input()
c.execute("SELECT * from pets WHERE name = '{0}' ".format(var))

For example, we can pass smth' OR '1' = '1 to var, and all of the data will be exposed.

The proper way of using the variables is by using DB-API's parameter substitution. To insert a variable into a query string, you should use placeholders or named parameters:

# Using placeholders
c.execute("INSERT into pets VALUES (?, ?, ?)", ("Tom", "cat", 2))

# Using named parameter
c.execute("SELECT * from pets WHERE name = pet_name", {"pet_name": "Leo"})

Conclusion

In this topic, we've learned the basics of raw querying. Now we know how to:

• Carry out CRUD operations;
• Create a connection to DB;
• How to use the cursor cursor.execute() method;
• How to use variables properly;

Also, we've learned several SQL keywords: