1713481526

Step-by-step tutorial on how to create and manipulate SQLite databases using Python


In this tutorial I want to take you step by step through how to create and manipulate SQLite databases using python in an easy and practical way. Here are the steps: ## 1. installation Make sure you have Python installed on your system. SQLite is already part of the standard Python library, so you don't need to install anything additional. ## 2. Importing the SQLite Module ```py import sqlite3 ``` ## 3. Connecting to the database To create or connect to an SQLite database, you can use the connect function of the sqlite3 module. If the database does not exist, it will be created automatically. ```py conexao = sqlite3.connect('exemplo.db') ``` ## 4. Creating a table After connecting to the database, you can create a table using a cursor object and executing a CREATE TABLE SQL statement. ```py cursor = connection.cursor() cursor.execute('''CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''') connection.commit() ``` ## 5. Inserting Data To insert data into a table, you can use the execute method with an INSERT INTO SQL statement. ```py cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30)) cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25)) connection.commit() ``` ## 6. Querying Data To query data from a table, you can use the execute method with a SELECT SQL statement. ```py cursor.execute("SELECT * FROM users") for row in cursor.fetchall(): print(row) ``` ## 7. Updating Data To update data in a table, you can use the execute method with an UPDATE SQL statement. ```py cursor.execute("UPDATE users SET age = ? WHERE name = ?", (35, 'Alice')) connection.commit() ``` ## 8. Deleting Data To delete data from a table, you can use the execute method with a DELETE SQL statement. ```py cursor.execute("DELETE FROM users WHERE name = ?", ('Bob',)) connection.commit() ``` ## 9. Closing the Connection Always close the connection after you're done working with the database. ```py connection.close() ``` For a better understanding, let's look at the complete code now ```py import sqlite3 # Connect to the database connection = sqlite3.connect('example.db') # Create a table cursor = connection.cursor() cursor.execute('''CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''') connection.commit() # Insert data cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30)) cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25)) connection.commit() # Query data cursor.execute("SELECT * FROM users") for row in cursor.fetchall(): print(row) # Update data cursor.execute("UPDATE users SET age = ? WHERE name = ?", (35, 'Alice')) connection.commit() # Delete data cursor.execute("DELETE FROM users WHERE name = ?", ('Bob',)) connection.commit() # Close connection connection.close() ``` Modify the code as you wish and test it in an application. thanks

To comment this publication you need to be logged in.