Working with databases
Working with databases in Python involves using a database library or module to interact with a database management system (DBMS). There are several libraries and modules available for working with databases in Python, including sqlite3, psycopg2, and MySQLdb.
Here's an example of how to use the sqlite3 module to work with a SQLite database in Python:
import sqlite3
# Connect to a database (creates the database if it doesn't exist)
conn = sqlite3.connect("test.db")
# Create a cursor
cursor = conn.cursor()
# Create a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY,
name TEXT,
salary REAL
)
""")
# Insert data into the table
cursor.execute("""
INSERT INTO employees (name, salary)
VALUES ('John Doe', 50000), ('Jane Doe', 55000)
""")
# Commit the changes to the database
conn.commit()
# Query the data from the table
cursor.execute("SELECT * FROM employees")
# Fetch the results of the query
employees = cursor.fetchall()
# Print the results
for employee in employees:
print(employee)
# Close the cursor and the connection
cursor.close()
conn.close()
In this example, the sqlite3 module is used to connect to a SQLite database, create a table, insert data into the table, and query the data from the table. The connect function is used to connect to the database and create a connection object. The cursor method is called on the connection object to create a cursor, which is used to execute SQL statements. The execute method is called on the cursor to execute SQL statements, and the commit method is called on the connection object to commit the changes to the database. The fetchall method is called on the cursor to retrieve the results of a query, and the close method is called on the cursor and connection objects to close them.
This is just a simple example of working with databases in Python using the sqlite3 module. Different databases may require different libraries or modules, and different SQL syntax may be required to perform similar operations, but the basic idea of connecting to a database, executing SQL statements, and fetching the results remains the same. Whether you're working with SQLite, PostgreSQL, MySQL, or another database, the concept of working with databases in Python is the same.
Leave a Comment