Lesson-15: Python Database
Python database
Python has many alternatives that you can use for database operations. But in all these alternatives, we will prefer SQLite. First of all, Sqlite is part of this language since versions 2.5 of Python. So if the version of Python you are using is 2.5 or higher, you can import SQLite like any module in Python and start using it.
In this lesson, we will use the sqlite3 module. This module is in our program:
import sqlite3we’ll import it in the form.
DB Browser for Sqlite3:
The reason we use this program is to see what we’re doing. Thanks to this program, we will see the tables that we have created in the database. We’ll check if we’re transferring data into the tables. You can click on the link below to download this program.
Create A New Database
In this section, we will learn how to create a new database through a module called sqlite.
Above, we mentioned a module called sqlite. So, as you can imagine, in order to use this module, we first need to import the module. As we said at the beginning of this section, Sqlite has been part of the language since Python version 2.5:
>>> import sqlite3
In Python, the module belonging to the Sqlite database system bears the name ‘sqlite3’. So we need to use import sqlite3 to import this module. If this name seems too long to you, or if you consider entering both numbers and letters as a chore because the numbers and letters in the module name are together, of course you know that you can also import the sqlite3 module with a different name. For example:
>>> import sqlite3 as sql
So we imported the sqlite3 module with the name ‘sql’. But while I’m explaining the topic, in order not to cause confusion from the reader’s point of view, I will pretend that we have imported the module as import sqlite3.
Let’s move on to how to create a database using this module… for this, we will use the connect() method of the sqlite3 module. We use this method as follows:
>>> vt = sqlite3.connect('database_name')
the argument named varitabani_name, which we give to the connect() method, is the name of the database that we will use. If a database with the specified name
>>> vt = sqlite3.connect('deneme.db')
If you have given this command, try it in the directory.if a database named sqlite does not exist, a database with that name will be created.
By the way, as the extension of the database file we chose db. There are no strict rules about what the database file extension should be. .instead of the DB extension, .sqlite3,.db or .there are also those who prefer extensions such as db3.
Experiment in the example above.a database file called sqlite is connected using the connect() method. Of course, if we wanted to, we could also give the connect() method the full file path as an argument:
>>> import sqlite3
>>> vt = sqlite3.connect('/home/btogrenme/test.sqlite') #GNU/Linux
>>> vt = sqlite3.connect('c:/users/bt/desktop/test.sqlite') #Windows
With the help of this command, we create a Sqlite database file on the hard disk.
Establish A Connection To An Existing Database
Above, we used a command like this to create a new Sqlite database called deneme.db:
>>> vt = sqlite3.connect('deneme.db')
If you have given this command, try it in the directory.if a database named db does not exist, a database with that name is created. If you already have a database file with this name, sqlite3 will connect to this database. So in SQLite, we use exactly the same code to both create a new database and connect to an existing database.
Creating A Cursor
Above, we learned how to connect to a database with Sqlite and how to create a new database using the connect() method.
the connect () method is the first step in the ability to perform operations on a database. After creating a database or connecting to an existing database, we need to create a cursor in the next step to be able to process the database.
To create a cursor, we will use a method called cursor() :
>>> im = vt.cursor()
After creating the cursor, we are now fully opening up. In this way, we will be able to execute SQL commands using the execute() method of the im object created above. How? Now let’s see.
Create A Table
As we said at the end of the previous section, after creating a cursor object, we can execute SQL commands using its execute() method. If you want, let’s try to understand what is what by making a simple example now. First, let’s import the necessary module:
>>> import sqlite3
Now let’s create a new database file (or connect to an existing database file)
>>> vt = sqlite3.connect('veritabani.db')
In order to process this database, let’s first create our cursor:
>>> im = vt.cursor()
Now let’s create a table in the database using the execute() method of the cursor created above:
>>> im.execute("CREATE TABLE adres_defteri (isim, soyisim)")
If you remember, we said that the Sqlite database system has a table-like structure, and every table in this system also has a name. Here’s what we do, create a table called ‘adress_defteri’ and add two columns called ‘name’ and ‘last name’ to this table. I mean, we’re actually creating something like this.:
| name | surname |
|---|---|
In addition, we do not forget that the name of this table we created is ‘adres_defteri’
Pay attention to how we do these operations. Where CREATE TABLE addres_defteri (name, surname) is a single character array. The CREATE TABLE part in this character array is an SQL command, which allows you to create a table.
Here we have written the CREATE TABLE statement in capital letters. But you can also write this expression in lowercase letters if you want. My goal in capitalizing here is to visually distinguish SQL commands from elements such as ‘address_defteri’, ‘name’, and ‘last name’. In other words, because I want the CREATE TABLE expression to be easily distinguished from the ‘adress_defteri’ element, I wrote the CREATE TABLE expression here in capital letters.
In the continuation of the character sequence, we see the expression (first name, last name). As you can imagine, these show the name of the column headers in the table. Accordingly, the table we created will have two different column headings named ‘name’ and ‘last name’.
By the way, it is a good idea to avoid using Turkish characters in the table name and column headers when creating Sqlite tables. Also, if you are going to use tags consisting of multiple words in the table name and column headers, either merge them together or enclose them in quotation marks. For example:
import sqlite3
vt = sqlite3.connect('person.db')
im = vt.cursor()
im.execute("""CREATE TABLE 'person file'
('personel name', 'personel surname', city)""")
I would also like to draw your attention to the fact that the SQL commands given as parameters to the execute() method are an ordinary array of characters. These have all the characteristics of character sequences in Python. For example, you can assign this array of characters to a variable before sending it to the execute() method:
import sqlite3
vt = sqlite3.connect('person.db')
im = vt.cursor()
sql = """CREATE TABLE 'person file'
('personel name', 'personel surname', city)"""
im.execute(sql)
View the contents of the person.db database file that you created using these codes using DB Browser for Sqlite and check whether the columns ‘staff Name’, ‘Staff name’ and ‘hometown’ actually occur.
By the way, when you run these codes a second time, you will receive an error message like this:
sqlite3.OperationalError: table 'personel dosyasi' already exists
It’s normal that you get this error message. Read on to learn how to overcome this…
Enter Data In A Table
So far, we have learned how to create a database using the sqlite3 module and how to place a table of various columns in this database. Now we will fill in the bottom of these column headers that we have created.
import sqlite3
vt = sqlite3.connect('vt.db')
im = vt.cursor()
tablo_yap = """CREATE TABLE IF NOT EXISTS personel
(isim, soyisim, memleket)"""
değer_gir = """INSERT INTO personel VALUES ('Fırat', 'Özgül', 'Adana')"""
im.execute(tablo_yap)
im.execute(değer_gir)
Here we learn another new SQL command called INSERT into table_name VALUES. In other words, the above sequence of characters means: “place the values ‘Euphrates’, ‘specific’ and ‘Adana’ in the staff. So create a table like this”:
| isim | soyisim | memleket |
|---|---|---|
| Fırat | Özgül | Adana |
Processing Of Data To The Database
In the previous section, we learned how to enter data into a Sqlite database. But in fact, it doesn’t just end with entering data. We need to take another step to “process” the data into the database. For example, let’s look at this example:
import sqlite3
vt = sqlite3.connect("vt.db")
im = vt.cursor()
im.execute("""CREATE TABLE IF NOT EXISTS
personel (isim, soyisim, sehir, eposta)""")
im.execute("""INSERT INTO personel VALUES
("Orçun", "Kunek", "Adana", "[email protected]")""")
Although we seem to have processed data in the database, there is actually nothing processed yet. If you want, you can use the Sqlitebrowser program to confirm this situation, and you can see for yourself that the data in the table is not processed.
We’ve just entered the data yet. But we didn’t process the data into the database. In order to process this data into the database, we will use a method called commit ().
>>> vt.commit()
As you can see, commit() is a method of the connection object (i.e., the VT variable here), not the cursor. Now let’s add this line to our script:
import sqlite3
vt = sqlite3.connect("vt.db")
im = vt.cursor()
im.execute("""CREATE TABLE IF NOT EXISTS
personel (isim, soyisim, sehir, eposta)""")
im.execute("""INSERT INTO personel VALUES
("Orçun", "Kunek", "Adana", "[email protected]")""")
vt.commit()
Bu son satırı da ekledikten sonra Sqlite veritabanı içinde şöyle bir tablo oluşturmuş olduk:
| isim | soyisim | şehir | eposta |
|---|---|---|---|
| Orçun | Kunek | Adana | [email protected] |
If we do not type row vt.commit , The database, table, and column headers are created, but the contents of the columns are not processed in the database.
Closing The Database
After completing all the operations that we will perform on the database, in principle, we must close this database. For example, let’s take the following codes:
import sqlite3
vt = sqlite3.connect("vt.db")
im = vt.cursor()
im.execute("""CREATE TABLE IF NOT EXISTS
personel (isim, soyisim, sehir, eposta)""")
im.execute("""INSERT INTO personel VALUES
("Orçun", "Kunek", "Adana", "[email protected]")""")
vt.commit()
vt.close()
Here, we used a method called close() to close the database after completing all operations:
vt.close()
In this way, from the moment the database is first opened, we release the resources that the operating system activates. In fact, when our program closes, all open Sqlite databases also close automatically. But it’s always a good idea to do this manually.
If you want to ensure that the database on which you are trading is automatically closed after everything is finished, you can use the word with, which we learned earlier:
import sqlite3
with sqlite3.connect('vt.db') as vt:
im = vt.cursor()
im.execute("""CREATE TABLE IF NOT EXISTS personel
(isim, soyisim, memleket)""")
im.execute("""INSERT INTO personel VALUES
('Fırat', 'Özgül', 'Adana')""")
vt.commit()
In this way, when we open a database connection using the word with, Python will automatically terminate the connection for us after all the work is done.
