{"id":2678,"date":"2024-01-02T18:03:25","date_gmt":"2024-01-02T18:03:25","guid":{"rendered":"https:\/\/blog.samarthya.me\/wps\/?p=2678"},"modified":"2024-01-02T18:24:14","modified_gmt":"2024-01-02T18:24:14","slug":"python-db","status":"publish","type":"post","link":"https:\/\/blog.samarthya.me\/wps\/2024\/01\/02\/python-db\/","title":{"rendered":"Python & DB"},"content":{"rendered":"\n

SQLite3<\/code> is a lightweight and easy-to-use database engine that is included with Python. It’s perfect for smaller-scale applications, prototyping, or standalone systems. Python’s sqlite3<\/code> module allows seamless integration with SQLite databases. <\/p>\n\n\n\n

Here’s a comprehensive guide on using Python 3 with SQLite3.<\/p>\n\n\n\n

Step 1: Importing the sqlite3<\/code> Module<\/h3>\n\n\n\n

Start by importing the sqlite3<\/code> module in your Python script:<\/p>\n\n\n\n

import sqlite3<\/code><\/pre>\n\n\n\n
Step 2: Connecting to a Database<\/h3>\n\n\n\n
To use an SQLite<\/code> database, establish a connection using sqlite3.connect()<\/code>:<\/p>\n\n\n\n
conn = sqlite3.connect('example.db')<\/code><\/pre>\n\n\n\n
Step 3: Creating a Cursor Object<\/h3>\n\n\n\n
Create a cursor object to interact with the database:<\/p>\n\n\n\n
cursor = conn.cursor()<\/code><\/code><\/pre>\n\n\n\n
Step 4: Creating a Table<\/h3>\n\n\n\n
Use the cursor to execute SQL queries. For instance, to create a ‘users’ table:<\/p>\n\n\n\n
cursor.execute('''CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL )''')<\/code><\/code><\/pre>\n\n\n\n
Step 5: Inserting Data<\/h3>\n\n\n\n
Insert data into the table using the execute()<\/code> method:<\/p>\n\n\n\n
cursor.execute(\"INSERT INTO users (name, email) VALUES (?, ?)\", ('John', 'John@example.com')) cursor.execute(\"INSERT INTO users (name, email) VALUES (?, ?)\", ('Doe', 'Doe@example.com'))<\/code><\/code><\/pre>\n\n\n\n
Step 6: Committing Changes<\/h3>\n\n\n\n
Save the changes made to the database:<\/p>\n\n\n\n
conn.commit()<\/code><\/code><\/pre>\n\n\n\n
Step 7: Retrieving Data<\/h3>\n\n\n\n
Retrieve data from the table using SQL queries:<\/p>\n\n\n\n
cursor.execute(\"SELECT * FROM users\") rows = cursor.fetchall() for row in rows: print(row)<\/code><\/code><\/pre>\n\n\n\n
Step 8: Closing the Connection<\/h3>\n\n\n\n
Close the connection to the database when finished:<\/p>\n\n\n\n
conn.close()<\/code><\/code><\/pre>\n\n\n\n
Conclusion<\/h3>\n\n\n\n
Python’s integration with SQLite3 makes it effortless to create, manipulate, and manage databases within your Python scripts. SQLite3’s simplicity and Python’s versatility make it an ideal choice for small to medium-scale projects or standalone systems where a lightweight, file-based database is sufficient.<\/p>\n\n\n\n