Create a Simple Table

A table in a database is a structured collection of data organized in rows and columns. Each column represents a specific type of information, and each row contains specific data. Tables are used for storing and organizing data in a database, allowing for various operations, including searching, filtering, sorting, updating, and deleting information.

Creating a table

cursor.execute(): This function executes an SQL query provided as a string. In this case, the SQL query is enclosed in triple single quotes ''' '''.

This part of the SQL query specifies the creation of a new table named "users". If a table with this name already exists (checked using IF NOT EXISTS), nothing will happen, and it won't result in an error. If the table doesn't exist, it will be created.

Note
Using IF NOT EXISTS, you're checking for the presence of the table before attempting to create it. If the table exists, the command won't result in an error, and the program will continue executing. If the table doesn't exist, it will be created. This helps avoid unexpected errors and ensures more stable database operations.

This portion of the SQL query defines the structure of the table. In this case, the "users" table will have three columns:

id and 'age' with the data type INTEGER, intended for storing numeric information, like the ages of users;
name with the data type TEXT, designed to store textual information such as user names.

So, this line of code creates the users table in the database with the specified column structure.

Saving the changes


              1234567891011
            
import sqlite3


conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT, age INTEGER)''')
print('The table was successfully created.')

conn.commit()
conn.close()

conn.commit() is like saving changes in a database. Until you commit (call commit), your changes are not permanent and won't be visible to other users of the database. After committing, the changes become permanent and are accessible to everyone. It's like hitting the "save" button for your database changes.

connection.close() is an essential step when working with SQLite database. This command closes the connection to the database after you've finished your operations.

Closing the connection is important for several reasons:

It releases resources: Closing the connection frees up the resources used for connecting to the database. This is crucial for efficient resource management and maintaining optimal program performance;
Ensures data persistence: After closing the connection, any unsaved changes to the data will be saved in the database, and they will be available when you reconnect;
Prevents potential errors: Closing the connection helps avoid conflicts and errors associated with attempting to perform operations on a closed connection.

Therefore, to use an SQLite database correctly and safely, it's important to always close the connection after you've completed your work with it.

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 2. Розділ 4