Getting Started

• we are going to use SQLite as our SQL database, because it is easy, no need complicated installation process.
• Basic of it, is same as other SQL database (MySQL or PostgresSQL).
• we are using Ubuntu 20.04 as our operating system.
• we are using python3.

Check if SQLite installed

In our ubuntu machine, open a terminal and write sqlite3.

jun@b:~$ sqlite3

If our system could't not find any sqlite3> prompt, we might need to install it.

jun@b:~$ sudo apt install sqlite3

After the installation finished, run sqlite3 command again and we will see something like this.

jun@b:~$ sqlite3

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> 

Noticed that, we got the sqlite3> prompt ? this means, we successfully installed it.

SQLite basic

Let's us, test creating a new table for our SQLite database. Using the prompt earlier, add the following SQL statement.

CREATE TABLE profiles ( name TEXT, sex TEXT, age INTEGER );

When we click <ENTER>, we might not see anything.

sqlite> CREATE TABLE profiles ( name TEXT, sex TEXT, age INTEGER );
sqlite> 

To see if we successfully created our table. write .tables on the prompt to list our tables. In this case, we only got one table named profiles.

sqlite> .tables
profiles

We can describes our tables, to look at it's schemas using the .schema command in sqlite prompt. It will show back the CREATE schemas we use to create the table.

sqlite> .schema profiles
CREATE TABLE profiles ( name TEXT, sex TEXT, age INTEGER );

This table that we recently create are stored in a memory (RAM) and not persistence. If we quite our sqlite program using Ctrl+D, all of our tables will be deleted. To make it persistence, we need to save if to a file.

To make our data persistance, we can use .open command in sqlite, to open a db file, and then run our create table command again to write to it.

• let's try open a db file named profiles_db.db and create our table

sqlite> .open profiles_db.db
sqlite> CREATE TABLE profiles ( name TEXT, sex TEXT, age INTEGER );

When we exited, our sqlite program, we can we a file was created in our current working directory.

jun@b:~$ ls
profiles_db.db

We can open our database, using the command sqlite3 <our_db_name.db> in terminal

jun@b:~$ sqlite3 profiles_db.db

or we can open it using .open <our_db_name.db> in sqlite prompt.

jun@b:~$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .open profiles_db.db

Executes SQL statements from .sql file

Let's say we have a insert-data.sql file contains sql statements to insert data into our table and the file is located within our working directory. We can execute that file using .read command on sqlite prompt.

• let's say our insert-data.sql contains the following statements

INSERT INTO profiles (name,sex,age) VALUES ("John","male",29);
INSERT INTO profiles (name,sex,age) VALUES ("Jane","female",24);
INSERT INTO profiles (name,sex,age) VALUES ("James","female",30);

• we can execute the statements like this, first we open previously create db, then run the .read command to read our insert-data.sql file. We can verified that data was inserted using sql SELECT statement.

sqlite> .open profiles_db.db
sqlite> .read insert-data.sql
sqlite> SELECT * FROM profiles;

John|male|29
Jane|female|24
James|female|30

sqlite> 

Connecting SQL database with Python

Python had a sqlite3 library that we can use to connect our python program with sqlite.

To use it, we simply import the library, and create a connection to our database file.

import sqlite3

#if db file doesn't exist yet, it will create one for use.
connection = sqlite3.connect('profiles_db.db') 

connection.close()

To execute SQL statements, we will need to create a cursor, and use cursor.execute() function to execute our statement.

import sqlite3

# we create connection
conn = sqlite3.connect("profiles_db.db")

# we create a cursor
cursor = conn.cursor()

# our SQL statement
stmt="SELECT * FROM profiles;"

# we execute our statement, and assign the results to an array
profileList = cursor.execute(stmt)

# Loop thru the list and print it out
for row in profileList:
    print(row)

conn.close() # we close connection

we can run it, and we see rows of profile get printed

jun@bin:~$ python3 profiles.py 
('John', 'male', 29)
('Jane', 'female', 24)
('James', 'female', 30)

• we can execute other various statements such as CREATE,SELECT,INSERT statements by simply write down the SQL statments and use cursor.execute() function to execute it.

import sqlite3

# we create connection
conn = sqlite3.connect("profiles_db.db")

# we create a cursor
cursor = conn.cursor()

# user we want to add
firstUser = ("Moriaty","male",23)
# our SQL statement
insert_stmt="INSERT INTO profiles (name,sex,age) VALUES (?,?,?)"
select_stmt="SELECT * FROM profiles;"

# we execute insert statement, with the value as second arguments.
cursor.execute(insert_stmt,firstUser)

# we execute our statement, and assign the results to an array
profileList = cursor.execute(select_stmt)

# Loop thru the list and print it out
for row in profileList:
    print(row)

conn.close() # we close connection

Running the script above, we see that we successfully inserted a row into our table

jun@bin:~$ python3 profiles.py 
('John', 'male', 29)
('Jane', 'female', 24)
('James', 'female', 30)
('Moriaty', 'male', 23)

We can also insert multiple data by using the same statements but change .execute to executemany.

import sqlite3

# we create connection
conn = sqlite3.connect("profiles_db.db")

# we create a cursor
cursor = conn.cursor()

# user we want to add
profiles = [
    ("Sherlock","male",23),
    ("Holmes","female",25)]

# our SQL statement
insert_stmt="INSERT INTO profiles (name,sex,age) VALUES (?,?,?)"
select_stmt="SELECT * FROM profiles;"

# we execute insert statement, with the value as second arguments.
cursor.executemany(insert_stmt,profiles)

# we execute our statement, and assign the results to an array
profileList = cursor.execute(select_stmt)

# Loop thru the list and print it out
for row in profileList:
    print(row)

conn.close() # we close connection

More info

• for more information feel free to read the official docs for sqlite3 library in python.