Skip to main content

Coding Python with SQLite DB

Got a homework assignment on Python is Easy coding class. The homework assignment can be fulfilled using 2 different method, the first one is uploading some code sample, and the other one is to write it in a blog. It turn out that i have started a blog and haven't look at it for a very long time 😞, so this might be the right time to start to write again. But, actually this is not orginally my writing. This is just a rewrite from python3 documentation but with my own code just to help me make sure that i have the correct understanding with the documentation python3 have.

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

Popular posts from this blog

Hidup itu Cuma Sekadar Minum

URIP iku mung sak dermo ngombe. Hidup itu cuma sekadar minum. Amat sangat singkat. Ibarat air baru membasahi tenggorokan, eh, sudah selesai. Tamat. Berulang kali Ayah dan Nenek mengingatkan saya. "Hati-hati Le, urip iku mung sak watoro, cuma sebentar,'' kata Nenek, penuh kasih. Sebagai manusia, diingatkan agar tidak dengki atau iri melihat keberuntungan orang lain. Sebab, kemampuan, kodrat, keadaan, dan keberadaan masing-masing orang itu berbeda. Ada lagi watak dahwen atau senang mencela orang lain, atau panasten alias senang menghalangi sukses orang lain. Hindari pula sifat angrong prasanakan, suka mengganggu istri orang. Dalam pupuh durma disebutkan, jangan terlalu banyak makan dan tidur, agar bisa mengurangi nafsu yang menyala-nyala. Kebenaran, kesalahan, keburukan, kebaikan, dan keberuntungan itu berasal dari perilaku kita sendiri. Untuk itu, tak usah memuji diri sendiri, dan jangan suka mencela orang lain. Ajining diri saka obahing lathi, seseorang itu dihargai karena...

Manajemen Upah dalam Perspektif Islam

Islam adalah Dien yang diturunkan oleh Allah untuk mengatur seluruh sendi kehidupan manusia, tidak hanya dalam hal ibadah secara vertikal namun juga hubungan antar manusia dan lingkungan secara horizontal yang dapat juga bernilai ibadah. Karena seluruh kegiatan manusia memiliki tuntunan dalam Islam, maka demikian pula halnya saat seseorang/lembaga ingin mempekerjakan orang lain pun sudah diatur dalam Islam tentang tatacara pengupahan dalam hubungan kerja tersebut. Karena bekerja adalah bagian dari ibadah, maka pekerja diajarkan untuk menyelesaikan pekerjaannya dengan ihsan dan itqan . Sebaik yang dia bisa lakukan dan berupaya untuk dapat memberikan hasil yang terbaik pula. Dengan konsep ihsan , yang di dalamnya ada muraqabah (rasa senantiasa diawasi oleh Allah SWT) maka tidak akan ada lagi penyalahgunaan sumber daya maupun waktu yang akan dilakukan oleh pekerja meskipun pengawasan dari pemberi kerja tidak dilakukan dengan ketat. Dengan konsep ihsan dan itqan ini pula maka pe...

Zhu Yuanzhang

Ini salah satu kisah tentang Zhu Yuanzhang (1328-1398), pendiri Dinasti Ming yang mengakhiri penjajahan Mongol atas Cina-Kaisar Hongwu. Beberapa tahun setelah dia bertahta (1368), kekaisaran baru yang dipimpinnya mengalami paceklik, dampak dari perang mengusir penjajah. Sang Kaisar prihatin, terlebih saat dia melihat, dalam kesengsaraan rakyatnya itu, beberapa pejabat & menteri masih hidup bermewah-mewah & berfoya-foya. Maka pada suatu hari, Sang Kaisar menyelenggarakan pesta ulang tahun permaisurinya dengan mengundang semua pejabat dan para menteri. Hadir pula para satrawan, sarjana, serta para panglima.  Saat masing-masing sudah menghadap meja makan, Sang Kaisar memanggil pelayan. "Sajikan hidangan pertama!", perintahnya.  Para dayang pun menghantarkan piring-piring berisi LOBAK REBUS. Semua yang hadir ternganga. "Ah", ujar Kaisar tertawa, "Leluhur mengatakan lobak lebih bagus daripada obat. Ada pepatah 'Lobak masuk ko...