Okay, so this time i will try to write some code sample using python to connect and work with SQLite database, i hope this will help me or others that try to learn python.
The library used to connect to a SQLite Database called 'sqlite3'.
But before that, first let's talk about why we need to connect to a database and specially why using SQLite.
When we start to code, sometimes or in many times we need to save the data we are working on in some sort of media. We might choose a text file for a very simple data recording media, but when we work with more complex data or with simple data but large number of it, we are going to need a more advance system to do that. This is the time when a database comes in handy.
A database with its rows and columns let us save and record data in a way that we can do more complex actions with that data. There are a lot of database system out there like MySQL, PostgreSQL, Oracle, SQLServer, and more, so the next question is why we should choose SQLite?
From python documentation i quote
"SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle."
From this paragraph i understand that SQLite is lightweight and can be used as a prototyping tools in the development process of an application lifecycle, and later on this chunk of code can then be replace when the developer needs to port their application to use a larger database.
So, how can we use SQLite Database in our python code?
First, we import the class in the library
import sqlite3
Next, we create a 'Connection' object from the database we want to connect with, the format to do that is like this "ConnectionVariableName = sqlite3.connect('database_name.db')"
this time i am going to use database named sample.db as our database
conn = sqlite3.connect('sample.db')
Now we have our connection with the database, we then create a 'Cursor' Object so we can use it later to do some SQL command with our database.
c = conn.cursor()
This cursor can be used to run the SQL command we want using 'execute()' method, for example i am going to create a table 'Cars' with four columns to record the maker, the model, production year, and the average of the weight of that car models.
c.execute(''' CREATE TABLE cars
(maker text, model text, year text, weight int) ''')
This command will tell python to execute our Cursor to do the SQL string we want to perform, but by this time, the database still not be affected with our code, we need to commit every Cursor execute command with a commit.
conn.commit()
Actually, we don't need to do commit on every command, we can execute multiple Cursor before we commit all of those SQL command, just remember that before we run commit on our connection, we are not changing our database yet, and any change or changes are not saved to the database.
After a few line of code, now i have a database, and a table. I can insert some data to the table using INSERT SQL statement. It will be like this
c.execute(''' INSERT INTO cars VALUES
("BMW", "520i", "2018", 1495) ''')
Most of the time, the data will come from a variable in python so we will be tempted to do it like this
maker = "BMW"
year = "2018"
c.execute("SELECT * FROM cars WHERE maker = '%s' AND year = '%s' " % (maker, year))
DO NOT do this, this is unsecure, it will lead our code to a vulnerable of a SQL injection attack. The python documentation encourage us to use parameter substitution and pass a tuple for that parameter like this code below
cParam = ("BMW", "2018")
c.execute("SELECT * FROM cars WHERE make = '?' AND year = '?'", cParam)
We can use this in every SQL command we want to perform, like an INSERT statement to append one data like this
cars = ("BMW", "520i", "2018", 1495)
c.execute("INSERT INTO cars VALUES (?, ?, ?, ?)", cars)
or an INSERT statement with multiple data that we pass the parameter in a list of tuple like this
cars = [("BMW", "520i", "2018", 1495),
("Ford", "Fiesta", "2019", 1169),
("Toyota", "Prius", "2020", 1317)]
c.executemany("INSERT INTO cars VALUES (?, ?, ?, ?)", cars)
So now we have a database, a table, and some data in it. In some of the code above we even execute a SELECT statement to retrieve data from our database, but how can we access the returned data from that SQL statement?
The answer is we can use multiple method to access it.
First we can iterate from the data that returned by our cursor execute command
for row in c.execute("SELECT * FROM cars ORDER BY year"):
print(row)
the second method we can use the 'fetchone()' method from our cursor. This method will return just one row as a tuple
myCars = ('Ford',)
c.execute("SELECT * FROM cars WHERE maker =?", myCars)
myCar = c.fetchone()
print(myCar)
and the last method we can use 'fetchall()' to get all the data, we will get a list of tuple from rows that returned by the performed SQL statement
c.execute("SELECT * FROM cars") myCar = c.fetchall() print(myCar)
and that's it. That is my sample code, if you want a complete explanation on sqlite3 python library you just need to go directly to python3 documentation page. Just click here to get there.
thank you for stopping by to my blog, see you again and happy learning. 😊
Comments