Python Database Interaction

How to Interaction Database in Python?

Interacting with databases in Python consists of the use of database connectors and executing SQL queries.

Here, I'll provide examples the use of SQLite, a light-weight database, and SQLAlchemy, a powerful ORM (Object-Relational Mapping) library for strolling with numerous databases.

SQLite Database Interaction:

Installing SQLite Connector:

                                  
                                    # For SQLite connector
pip install sqlite-connector-python

Connecting to a SQLite Database and Executing Queries:

                                  
                                    import sqlite3

# Connect to a SQLite database (create if not exists)
conn = sqlite3.connect('example.db')

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT,
        email TEXT
    )
''')

# Insert data
cursor.execute("INSERT INTO users (username, email) VALUES (?, ?)", ('john_doe', 'john@example.com'))

# Commit changes
conn.commit()

# Query data
cursor.execute("SELECT * FROM users")
users = cursor.fetchall()
print(users)

# Close the connection
conn.close()

SQLAlchemy Database Interaction:

Installing SQLAlchemy:

                                  
                                    pip install sqlalchemy

Connecting to a Database Using SQLAlchemy:

                                  
                                    from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Define the database model
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)

# Create an SQLite database in memory
engine = create_engine('sqlite:///:memory:')

# Create tables
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Insert data
new_user = User(username='jane_doe', email='jane@example.com')
session.add(new_user)
session.commit()

# Query data
users = session.query(User).all()
for user in users:
    print(user.username, user.email)

These examples cover fundamental database interactions with SQLite and SQLAlchemy in Python.

Depending to your undertaking requirements, you can select exclusive database connectors or ORMs.

Always make certain proper managing of database connections, use parameterized queries to save you SQL injection, and look at first-rate practices for safety and average performance.