Hi all! Today we will figure out how to work with the SQLlite database in Python and what it is all about. So…

Usually, a server is used to create a database, which must be properly configured, installed, etc., etc., etc. … BUT! There is SQLlite – which is not only an open source development, but also allows you to use databases without a server. How? Very simple – the entire database is just a file that can be easily transferred or distributed. This makes it very suitable for use in mobile, embedded systems, and web applications where a minimum number of dependencies and installations are required.

Now let’s figure out how to work with SQLlite in Python. To do this, we need the sqlite3 library, more details about which you can find here.

Database connection:

import sqlite3
conn = sqlite3.connect('example.db')

It is important to know that the ‘example.db’ file will be created automatically if it does not already exist. If the file already exists, the connection to that file will be established.

Table creation:

cursor = conn.cursor()
cursor.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)''')
conn.commit()

What is the cursor in this code? Let’s figure it out 😉

This code is talking about a SQLite “cursor”. The cursor is a tool that allows you to perform operations on the database.

conn.cursor() creates a cursor instance that can be used to query the database. In this case, cursor.execute is used to execute a query to create the ‘users’ table. Finally, conn.commit() commits the changes to the database.

Now consider the command “cursor.execute(“‘CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)”‘)”. What she does?
This command creates the “users” table in the SQLite database using the “cursor” cursor.

“CREATE TABLE users” is a command to create a table named “users”.
“(id INTEGER PRIMARY KEY, name TEXT, email TEXT)” is a list of columns in a table where:
“id” – primary key, integer.
“name” is a column of text data.
“email” is a column of text data.
After executing the command, the table will be created in the database and ready for adding data.

Now you need to insert data into the resulting table 😉
3. Data insertion:

cursor = conn.cursor()
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('John', 'john@example.com'))
conn.commit()

This code inserts data into the “users” table. The “INSERT INTO” command is executed, which adds data to the specified “users” table. The parentheses indicate the names of the columns where the data will be inserted. The values “John” and “john@example.com” are specified as question marks and are passed as arguments at the end of the command.

Now, for example, let’s get the data.
4. Data sampling:

cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)

In this code, the cursor “cursor” is first created using the “conn.cursor()” method. The SQL query “SELECT * FROM users” is then executed, which selects all the data from the “users” table. The result of the query is stored in the “rows” variable. We then run a loop, iterating through each row in “rows” and printing each row with “print(row)”.

And finally, after working with the database, you need to close the connection to the SQLlite database.
5. Closing the connection:

conn.close()

Why close the connection to the SQL database? It’s very simple: we close the database connection to free up resources and prevent possible problems with access to the database.

Phew. Enough for today!

As always, in case of questions, write to the mail or Telegram.