"/home/yossef/notes/full-stack/python-backend/fastapi/curd_sql/database.md"

path: full-stack/python-backend/fastapi/curd_sql/database.md

- **fileName**: database
- **Created on**: 2025-04-15 17:17:29

creating connection with mysql database using PyMySQL sqlalchemy

from sqlalchemy import create_engine, text
from sqlalchemy.orm  import declarative_base, sessionmaker

engine = create_engine(
    'mysql+pymysql://yossef:yossef280@localhost:3306/db_fastapi'
    # ('mysql+pymysql://<user>:<password>@<host>:<port>/<database>')
    # connect_args={"check_same_thread": False},  # only needed for SQLite

)

## check for error happend in connection to db
try:
    with engine.connect() as connection:
        result = connection.execute(text("SELECT DATABASE()"))
        database_name = result.scalar_one()
        print(f"Successfully connected to database: {database_name}")

except Exception as e:
    print(f"Error connecting to MySQL: {e}")

## creating a session for interactive with mysql
## autocommit -> must do session.commit() for save changes
## autoflush:  you make to your mapped objects within the session are not 
##      immediately written to the database.
SessionLocal = sessionmaker(autoflush=False, autocommit=False, bind=engine)

## You will then inherit from this Base class when defining your SQLAlchemy
## models (classes that represent database tables). 
Base = declarative_base()

"""
Base = declarative_base()

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    description = Column(String, nullable=True)
    owner_id = Column(Integer) # Example foreign key
"""

continue:./models.md
before:./scheme.md