Python + Databases

mkgfabo

— 3 min read
Python + Databases

This example shows how to connect to database from Python. Imagine you're organizing a library:

RDBMS (Relational Database Management System): Think of an RDBMS as a giant, organized filing cabinet for your library. Each drawer represents a table, and each folder in the drawer represents a record.

For example:

  • One drawer (table) for "Books"
  • Another drawer (table) for "Authors"
  • A third drawer (table) for "Borrowers"
  1. ORM (Object-Relational Mapping) with SQL Alchemy: Now, imagine you have a magical librarian assistant (that's SQL Alchemy) who can understand both your human language and the filing cabinet's organization system.

Instead of manually searching through the filing cabinet, you can tell your assistant things like:

"Find me all books by J.K. Rowling" or "Add this new book to our collection"

The assistant (SQL Alchemy) translates your request into the proper "filing cabinet language" (SQL) and retrieves or stores the information for you.

Here's a simple example of how you might use SQL Alchemy in Python code:

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

Base = declarative_base()

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    author = Column(String)

# Create the "filing cabinet"
engine = create_engine('sqlite:///library.db')
Base.metadata.create_all(engine)

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

# Add a new book
new_book = Book(title="Harry Potter", author="J.K. Rowling")
session.add(new_book)
session.commit()

# Find all books by J.K. Rowling
rowling_books = session.query(Book).filter_by(author="J.K. Rowling").all()
for book in rowling_books:
    print(book.title)

In this example, SQL Alchemy allows you to work with your database (the filing cabinet) using Python objects and methods, rather than writing complex SQL queries. It's like having a super-smart librarian who does all the hard work of organizing and retrieving information for you!

Changing from SQLite to PostgreSQL is relatively simple with SQLAlchemy. Here's how you can modify the previous example to use PostgreSQL instead:

  1. First, you'll need to install the PostgreSQL adapter for Python. You can do this by running:

`pip install psycopg2-binary`

  1. Then, you only need to change the database connection string in your code. Here's how the modified code would look:
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String)
    author = Column(String)

# Change this line to use PostgreSQL
engine = create_engine('postgresql://username:password@localhost:5432/library')

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# The rest of your code remains the same
new_book = Book(title="Harry Potter", author="J.K. Rowling")
session.add(new_book)
session.commit()

rowling_books = session.query(Book).filter_by(author="J.K. Rowling").all()
for book in rowling_books:
    print(book.title)

The main change is in the `create_engine` line. Let's break down the new connection string:

  • `postgresql://`: This specifies that we're using PostgreSQL.
  • `username:password`: Replace these with your actual PostgreSQL username and password.
  • `@localhost:5432`: This is the host and port where PostgreSQL is running. 5432 is the default PostgreSQL port.
  • `/library`: This is the name of your database.

So, if your PostgreSQL username is "myuser", password is "mypassword", and you want to use a database called "mylibrary", your connection string would look like this:

engine = create_engine('postgresql://myuser:mypassword@localhost:5432/mylibrary')

That's it! The rest of your code can remain exactly the same. This is one of the big advantages of using an ORM like SQLAlchemy – you can switch between different types of databases with minimal changes to your code.

Remember, you'll need to have PostgreSQL installed and running on your system, and you should have created the database you're connecting to ('library' in this example) before running this code.

Read more