Inserting into a Database with SQLite
Welcome to an SQLite mini-series! SQLite, as the name suggests, is a lite version of an SQL database. SQLite3 comes as a part of the Python 3 standard library.
Databases offer, typically, a superior method of high-volume data input and output over a typical file such as a text file. SQLite is a "light" version that works based on SQL syntax. SQL is a programming language in itself, but is a very popular database language. Many websites use MySQL, for example.
SQLite truly shines because it is extremely lightweight. Setting up an SQLite database is nearly instant, there is no server to set up, no users to define, and no permissions to concern yourself with. For this reason, it is often used as a developmental and protyping database, but it can and is used in production. The main issue with SQLite is that it winds up being much like any other flat-file, so high volume input/output, especially with simultaneous queries, can be problematic and slow. You may then ask, what really is the difference between a typical file and sqlite. First, SQLite will let you structure your data as a database, which can easily be queried, so you get that functionality both with adding new content and calling upon it later. Each table would likely need its own file if you were doing plain files, and SQLite is all in one. SQLite is also going to be buffering your data. A flat file will require a full load before you can start querying the full dataset, SQLite files don't work that way. Finally, edits do not require the entire file to be re-saved, it's just that part of the file. This improves performance significantly. Alright great, let's dive into some SQLite.
First, we need to establish a connection and cursor. This is true with both SQLite and MySQL:
import sqlite3
conn = sqlite3.connect('tutorial.db')
c = conn.cursor()
In our definition of the connection, you see we're connecting to a database, called 'tutorial.db.' This didn't exist prior in my case, but, when we run the code, if the database doesn't exist, it will be created. If it does exist, it will not be overwritten or re-created. Next, we defined the cursor. Think of the cursor like your mouse cursor, it simply does things, like select things, delete things, add things, and so on. Now, most people think of a database, and think of rows and columns of data. That's actually a table. Tables go in databases, and data goes in the tables. A database may only contain a single table, or it may contain a thousand tables. Let's make a table:
def create_table():
c.execute("CREATE TABLE IF NOT EXISTS stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)")
Above, we begin with our first actual SQL query. SQL is a language of its own. This means, when you learn to use SQL with Python, you already know how to build SQL queries, even if you're doing it in a new language. While not required, generally, people will use all-caps to denote SQL-specific commands, since an SQL query contains both SQL elements and dynamic ones that you set. Since SQL queries are strings, sometimes they can be hard to debug without some sort of differentiation like this. Note, SQLite is not blind to casing, but MySQL is. Python and most programming languages are not blind to casing. Remember this, and the difference between SQLite and MySQL.
The above code creates a table, called stuffToPlot, if it doesn't exist. This table contains the following rows: unix, datestamp, keyword, and value. Each column is assigned a datatype. In our case, unix is a REAL, which is like a Python float, then we have some TEXT variables, and another REAL. Check out SQLite datatype documentation for the available datatypes. SQLite only has 5 major types. MySQL has many multiples more. You may be curious why all variables are not something like "text" or why we don't use "blob" for everything. The idea is that, if we know it will be only integers, then we can assign a datatype that will help to keep the file size as compressed as possible, as well as keeping input/output operations as quick as possible. Speaking of some IO, let us create some input:
def data_entry():
c.execute("INSERT INTO stuffToPlot VALUES(1452549219,'2016-01-11 13:53:39','Python',6)")
conn.commit()
c.close()
conn.close()
create_table()
data_entry()
Here, the cursor executes an SQL query. This one is an INSERT INTO, and the table name follows. Then, we insert a tuple of values. After inserting we use conn.commit(). Think of conn.commit() much like saving the document. Recall how SQLite works. You have a file part before actually committing. You do not need to commit after every INSERT. Instead, you commit when you are done with that specific insertion task. You then close the cursor, and connection when you are totally done. If you may be doing more inserts in a moment, then there's no reason to close the connection. If instead you are using SQLite on a registration page, for example, once the user has registered, you wouldn't want to leave that connection open wasting memory, you'd want to close it off.
Finally, in the code above, we run the functions, creating the table and entering a row. All set. How do we know it's done? We could run another SQL query to request some data, but you may want to visually see your table from time to time. This can be done in a variety of ways, but I prefer and recommend: SQLite Browser
Full code to this point:
import sqlite3
conn = sqlite3.connect('tutorial.db')
c = conn.cursor()
def create_table():
c.execute("CREATE TABLE IF NOT EXISTS stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)")
def data_entry():
c.execute("INSERT INTO stuffToPlot VALUES(1452549219,'2016-01-11 13:53:39','Python',6)")
conn.commit()
c.close()
conn.close()
create_table()
data_entry()
Now, we've inserted some data, but this is rather inefficient for us to have to be manually typing the data we're going to be intering. In the next tutorial, we're going to talk about how to dynamically insert variables.
