Python SQLite

Python SQLite

SQLite is a C library that provides a lightweight, serverless, self-contained, high-reliability, and full-featured SQL database engine. Python comes with SQLite3 as a standard library, which allows you to work with a SQLite database with ease.

Here's a basic guide to get started with SQLite in Python:

1. Creating a New SQLite Database and a Table

import sqlite3 # Connect to a database (or create one if it doesn't exist) conn = sqlite3.connect('my_database.db') cursor = conn.cursor() # Create a new table cursor.execute(''' CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER ) ''') # Commit the changes and close the connection conn.commit() conn.close()

2. Inserting Data into the Table

conn = sqlite3.connect('my_database.db') cursor = conn.cursor() # Insert a single record cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30)) # Insert multiple records users = [("Bob", 25), ("Charlie", 35), ("David", 40)] cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users) conn.commit() conn.close()

3. Fetching Data from the Table

conn = sqlite3.connect('my_database.db') cursor = conn.cursor() # Fetch all records cursor.execute("SELECT * FROM users") all_rows = cursor.fetchall() for row in all_rows: print(row) # Fetch a single record cursor.execute("SELECT * FROM users WHERE name=?", ("Alice",)) alice_data = cursor.fetchone() print(alice_data) conn.close()

4. Updating and Deleting Data

conn = sqlite3.connect('my_database.db') cursor = conn.cursor() # Update a record cursor.execute("UPDATE users SET age=? WHERE name=?", (31, "Alice")) # Delete a record cursor.execute("DELETE FROM users WHERE name=?", ("Bob",)) conn.commit() conn.close()

5. Handling Transactions

You can use the commit() method to save changes and the rollback() method to revert changes in case of an error:

try: # Make some database changes conn.commit() except: # Rollback in case of any error conn.rollback() finally: conn.close()

These are just basic operations. SQLite in Python offers much more, including support for creating custom functions, using advanced SQL features, and handling database transactions. Always make sure to close the connection after you're done with database operations to free up resources.

More Tags

micro-frontend git-push seed android-jetpack-navigation byte-order-mark pgadmin-4 apache-httpcomponents resthub activemq-classic stm32f0

More Programming Guides

Other Guides

More Programming Examples