Creating 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. Let's look at an example of creating a table:

Executing SQL Queries

The cursor.execute() function is used to execute an SQL query provided as a string. In this example, we pass an SQL query to the function to create table. The SQL query is enclosed in triple single quotes (''' ''') for easier reading. In the next section, we will look at other examples of using cursor.execute() function.

Note that in SQL scripts, extra spaces and indentations are used solely to improve readability and code organization without affecting execution. This differs from languages like Python, where spaces and indentation are critical and define the program’s structure.

This course does not require prior knowledge of SQL, but basic knowledge of SQL is a plus for any developer. Therefore, these courses might be of interest to you:

Saving the Сhanges

In this script, we connect to the database and create a table. An essential final step when working with the sqlite3 library is committing and closes the connection. Let's take a detailed look at how this is done.


              1234567891011121314
            
import sqlite3  

# Establish a connection to the database (or create it if it doesn't exist)
conn = sqlite3.connect("my_database.db")  
# Create a cursor object to interact with the database
cursor = conn.cursor()  
# Execute an SQL query to create the `users` table if it doesn't already exist
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT, age INTEGER)")  
print("The table was successfully created.")  

# Commit the transaction to save changes to the database
conn.commit()  
# Close the database connection
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.

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

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 4