SQLite is a zero-configuration SQL database engine and can also be considered as a file-based database. It is a very small and lightweight database which has no external dependencies. It can be a very useful tool for data analysis process as you can create RDBMS structure from within your code and then run queries on the whole table in an efficient manner,  without any need of server installation or configuration. We will explore SQLite basics in this tutorial. Python has an in-built sqlite3 module, which can be used to work with SQLite database. We will explore following things in this tutorial:-    1. Creating a table    2. Inserting values in the table    3. Selecting values from the table

1. Creating a table

First of all, we will first create an empty table in the SQLite database.  We will first create an empty table in the sqlite database.

• import sqlite3 – It is importing the sqlite3 module. This is an in-built module in your python, so you don’t need to install it.
• conn = sqlite3.connect(‘sql_tutorial.db’) – We are establishing a connection with the database named as sql_tutorial. File type for it is .db. If this database doesn’t exist at the location, then it will create a new database with this name.
• c = conn.cursor() –  It will start a cursor
• c.execute() – Any SQL query we write, need to be in this function as this will run those queries and then return the output. We are creating a table named yoursData with four columns i.e. rowid, page, visits, average_time_spent. One thing to notice here is “IF NOT EXISTS” part of the CREATE query. It denotes that if mentioned table name doesn’t exist then create the table, else it will skip the query. It helps in rerunning the code, and avoid the CREATE statement error which will happen if the table name is already there.
• c.close() – It will close the cursor.
• conn.close() – You should always close the database connection after being done with it. It frees up the memory.

2. Inserting values in a SQLite table

There are two ways in which values can be inserted in the table i.e.by hard coding the values and dynamically.Let’s look at inserting hard-coded values:-  Let’s look at inserting hard-coded values:-

• As mentioned earlier, we are writing our SQL query in the c.execute().
• INSERT statement is the basic SQL statement for insertion and it will insert values in the columns. Values will be inserted like this :- rowid = 1, page = 4, visits = 150, average_time_spent = 2.5

We can also mention columns while inserting values. The only thing to take care here is that number of columns mentioned should be equal to the number of values given. If there is any mismatch, it will throw an error.

 This will match columns with values and insert. It is not necessary to give columns name in the same order as in the table. For e.g.c.execute(“INSERT INTO yoursData (page, rowid,  visits, average_time_spent) VALUES(4,1, 150, 2.5)”) is same as the above. We can also insert values into the table dynamically.  We will use a loop to insert 100 values in the table. 

• Created four variables to insert values dynamically in the table.
• In the c.execute block, we can insert values by referencing these variables.
• In SQLite, variables can be referenced in a query by using “?” sign. It is same for all the data types.
• conn.commit() – As we are inserting values and changing the database, we need to commit those changes to save it.

3. Selecting values from a SQLite table

Now, we will explore the read operation in SQLite.

• The SELECT statement is used for reading from the table.
• We will write our SQL query in c.execute().
• It will select all the values from the table, but to see it we will need to use print function.
• c.fetchall() is fetching all the rows selected by the SELECT statement and saving it in data variable
• We are printing the data variable, which contains all the rows of the table.
• You can see that values are not in rows format. We can change it so that it will print rows in a separate line which will make it easier to read.

• We can also reference the required column name in the SELECT statement. It will help in saving memory as we will be fetching only the required records.

• We can also use conditions while selecting values from the table. Conditions need to be mentioned after WHERE statement and it has all of the SQL default operators i.e. AND, OR etc. available.

• We can also use indexing to access any particular column.

 As we are only accessing row[0], it will print the first column. We have covered basic concept of SQLite e.g. creating a table, inserting values and reading from the table in this tutorial. We will cover other advanced concepts of SQLite in the next post.Let us know if there is any doubt or suggestion regarding this.