#C SQLITE DATABASE TUTORIAL HOW TO#
#C SQLITE DATABASE TUTORIAL FULL#
#C SQLITE DATABASE TUTORIAL CODE#

You then close the cursor, and connection when you are totally done. Instead, you commit when you are done with that specific insertion task. You do not need to commit after every INSERT.

You have a file part before actually committing. Think of mit() much like saving the document. This one is an INSERT INTO, and the table name follows. Speaking of some IO, let us create some input:Ĭ.execute('INSERT INTO stuffToPlot VALUES(1452549219,' 13:53:39','Python',6)')

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. You may be curious why all variables are not something like 'text' or why we don't use 'blob' for everything. Check out SQLite datatype documentation for the available datatypes. In our case, unix is a REAL, which is like a Python float, then we have some TEXT variables, and another REAL. This table contains the following rows: unix, datestamp, keyword, and value.

#C SQLITE DATABASE TUTORIAL CODE#

The above code creates a table, called stuffToPlot, if it doesn't exist. Remember this, and the difference between SQLite and MySQL. Python and most programming languages are not blind to casing. Note, SQLite is not blind to casing, but MySQL is. Since SQL queries are strings, sometimes they can be hard to debug without some sort of differentiation like this. 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.

#C SQLITE DATABASE TUTORIAL HOW TO#

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. Let's make a table:Ĭ.execute('CREATE TABLE IF NOT EXISTS stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)')Ībove, we begin with our first actual SQL query. A database may only contain a single table, or it may contain a thousand tables. Tables go in databases, and data goes in the tables. Now, most people think of a database, and think of rows and columns of data. Think of the cursor like your mouse cursor, it simply does things, like select things, delete things, add things, and so on. If it does exist, it will not be overwritten or re-created. 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. Alright great, let's dive into some SQLite.įirst, we need to establish a connection and cursor. Finally, edits do not require the entire file to be re-saved, it's just that part of the file.

#C SQLITE DATABASE TUTORIAL FULL#

A flat file will require a full load before you can start querying the full dataset, SQLite files don't work that way. SQLite is also going to be buffering your data. Each table would likely need its own file if you were doing plain files, and SQLite is all in one. 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. You may then ask, what really is the difference between a typical file and sqlite.

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. For this reason, it is often used as a developmental and protyping database, but it can and is used in production. 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. SQLite truly shines because it is extremely lightweight. SQL is a programming language in itself, but is a very popular database language. SQLite is a 'light' version that works based on SQL syntax. SQLite3 comes as a part of the Python 3 standard library.ĭatabases offer, typically, a superior method of high-volume data input and output over a typical file such as a text file. Welcome to an SQLite mini-series! SQLite, as the name suggests, is a lite version of an SQL database.

C sqlite database tutorial