When do you use SQLite

Basic procedure for using database access (here SQLite)

There is a basic procedure for using databases with Python, which we would like to see here in 5 steps. The procedure is almost identical for most of the available databases (in the following: SQLite). In the following chapter we set up a connection to the database and perform an action with the database (in the example we create an empty table).

We start with SQLite because it offers us several advantages (as mentioned in the introductory description). It already exists in Python and the module can easily be imported. The data itself is saved in a file with the file extension “.db” and can therefore be passed on without any problems.

first step: import the SQL module

The first thing we need to do is import our SQL module:

The following 5 steps are usually necessary for interacting with databases:

  1. Establish a connection to the database
  2. Cursor object (to access the database)
  3. Submit SQL query
  4. commit: we confirm to the database that we really want to execute the SQL statement
  5. Closing the database connection (must be in order)

Step 1: Establish our connection to the database

We now want to establish a connection to our database. For this we need a database name, which we can then access via a variable (pointer).

Let's look at the command first and then comes the explanation:

So we establish a connection (English "connection" and the verb to connect "connect") to our database "geburtstage.db". If this database does not yet exist, our database system "SQLite" automatically creates a file in the same folder the first time it is called up.

If you do not want to have it in the same folder as the Python programs, then simply specify the desired subfolder (the subfolder should already be created):

If the folder is not created, creating the database fails and we get the error message: "sqlite3.OperationalError: unable to open database file"

So just create a folder in the operating system before we release our command on it!

Step 2: cursor object cursor ()

The next step is to set up the cursor object. What is a cursor anyway? You've always used the cursor on the screen, but you've never wondered where that word came from. Like most from either Latin or Greek. In Latin, the word cursor has the meaning of "runner". The current machining position is shown on the screen with the cursor. The same with the database cursor - but related to the database record. It therefore shows the current position when reading or writing data records.

We now want to create such an object:

From now on we can use our pointer.

Step 3: Create SQL code and send it to the database: execute ()

Now we can pass our SQL statement to the database. With this statement we tell the database what to do: For example, whether a new data record is to be created or the database should return certain data records so that we can display them. The transfer takes place as a string. The SQL statement is executed with the statement.

So far we have a database with the name "geburtstage.db". However, we don't have any tables in the database yet. Basically, the structure of our command would be:

What is now in our SQL statement?

As in Excel, a table consists of rows and columns. In Excel, the names "A, B, C, ... AA, AB" are automatically used for the columns. That would be rather impractical for us. In contrast to Excel, we assign for our database for each column:

  • a name (so that we can address the "column")
  • the type of content (string, integer, date, etc.)
  • the field length

We create ("create") a table ("table") which has a name.

An SQL statement is also prepared as a string and this string is then transferred to the statement. This makes the SQL statement easier to read and errors cannot easily creep in. So again the same instruction as above:

Step 4: Execute the instruction with

When changing data in the database, a further confirmation is required for execution. The passed SQL statement must now be executed. For this purpose, the command informs the database system that the statement may finally be executed. In our example, the table is created:

Step 5: close the connection with close ()

Cleaning up is always a good thing. So we close the connection after the work is done using the instruction

Create complete code for the table

And here is our complete code:

Now we can run our program and have our database created. Even if there is no content yet, the “geburtstage.db” file is created and has a file size (the prepared structure is now in it).

If we run the code again, we get an error message:

sqlite3.OperationalError: table people already exists

We can avoid this error by only executing the SQL statement if a table does not yet exist. We extend our SQL statement to include "IF NOT EXISTS":

Recommend • Social Bookmarks • Thank you