<jupyter_start><jupyter_text>SQLAlchemy SQLAlchmey & Django have a similar object-relational mappings (aka `ORM`) are ways to connect Python to a SQL database.<jupyter_code># https://www.sqlalchemy.org/
class Movie1:
    name = 'Unknown'
    genre = 'Action'
   
    def __init__(self, name='', *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.name = name
movie_obj = Movie1(name="The Matrix", genre="Sci-Fi")
movie_obj.name
from dataclasses import dataclass

@dataclass
class Movie:
    name:str = 'Unknown'
    genre:str = 'Action'
    year:int = None
movie_obj = Movie(name="The Matrix", genre="Sci-Fi")
movie_obj.name
movie_obj.genre
data = [
    {
        "name": "Interstellar",
        "genre": "Sci-Fi"
    },
    {
        "name": "The Martian",
        "genre": "Sci-Fi"
    },
    {
        "name": "Arrival",
        "genre": "Sci-Fi"
    }
]
df = pd.DataFrame(data)
df.head()
# sqlalchemy
# https://docs.sqlalchemy.org/en/14/intro.html#installation
# https://docs.sqlalchemy.org/en/14/
# https://docs.sqlalchemy.org/en/14/tutorial/index.html
# 
# https://www.tutorialspoint.com/sqlalchemy/index.htm <-----
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
engine = create_engine("sqlite:///app_test.db")  # mysql, postgres
Session = sessionmaker(bind=engine)
my_sess = Session()
# SE DECLARA LA CLASE, Base
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Movie(Base): # table
    __tablename__ = "movies" # TABLE NAME
    
    id = Column(Integer, primary_key=True) # auto created for us
    name = Column(String) # 'Unknown'  # COLUMN
    genre = Column(String) # 'Action'  # COLUMN
    description = Column(String) # 'Action'  # COLUMN
    year = Column(Integer, nullable=True) # None  # COLUMN
    
    
    def __repr__(self):
        return f"<Movie name={self.name}>"

# saved instance = row
# ADD TABLE(S) TO DATABASE
Base.metadata.create_all(engine)
movie_obj = Movie(name='Interstellar', genre='Sci-Fi')
print(movie_obj.name)
my_sess.add(movie_obj) # prepare to save
my_sess.commit() # save
movie_obj.id
movie_obj2 = Movie(name='The Matrix', genre='Sci-Fi')
print(movie_obj2.name, movie_obj2.id)
movie_obj3 = Movie(name='Lovecraft Country', genre='Sci-Fi')
print(movie_obj3.name, movie_obj3.id)
my_sess.add(movie_obj2) # PREPARE TO SAVE
my_sess.add(movie_obj3) # PREPARE TO SAVE
my_sess.commit() # SAVE
print(movie_obj2.id, movie_obj3.id)<jupyter_output>11 12<jupyter_text>CRUD in SQLAlchemy    - Create    - Retrive    - Update    - Delete<jupyter_code># SE CREA OTRA SESION
Session = sessionmaker(bind=engine)
session = Session()
# CREATE
movie = Movie(name='Guaridans of the Galaxy')
session.add(movie)
session.commit()
# RETRIVE

# GET 1 ITEM
movie_a = session.query(Movie).get(2)
print(movie_a.id, movie_a.name, movie_a.description)
# LIST
# qs -> Query Set
qs = session.query(Movie).all()
print(qs)
# LIST & FILTER BY COLUMN VALUE
qs = session.query(Movie).filter_by(name='Lovecraft Country').all()
print(qs)
# LIST & FILTER BY COLUMN VALUE CONTAINING SOMETHING
qs = session.query(Movie).filter(Movie.name.contains("Galaxy")).all()
qs
# LIST & FILTER BY COLUMN VALUE CONTAINING SOMETHING
my_query = input("What are you looking for?\n") or "Unknown"
qs = session.query(Movie).filter(Movie.name.contains(my_query)).all()
print(qs)
# UPDATE

movie_a = session.query(Movie).get(2)
movie_a.description = "Cool movie"
print(movie_a.id, movie_a.name, movie_a.description)
session.commit()
movie_a = session.query(Movie).get(2)
print(movie_a.description)
# RENAME A MOVIE
qs = session.query(Movie).filter(Movie.name.contains("Matrix")).all()
for movie_obj in qs:
    movie_obj.name = "Matrix Revolutions"
session.commit()
qs = session.query(Movie).filter(Movie.name.contains("Matrix")).all()
qs
# DELETE
movie_a = session.query(Movie).get(7)
print(movie_a)
session.delete(movie_a)
session.commit()
session.flush()
qs = session.query(Movie).all()
print(qs)<jupyter_output>[<Movie name=Lovecraft Country>, <Movie name=Guaridans of the Galaxy>, <Movie name=Interstellar>, <Movie name=Matrix Revolutions>, <Movie name=Lovecraft Country>, <Movie name=Guaridans of the Galaxy>, <Movie name=Interstellar>, <Movie name=Interstellar>, <Movie name=Matrix Revolutions>, <Movie name=Lovecraft Country>, <Movie name=Guaridans of the Galaxy>]<jupyter_text>Hacer __ingenieria inversa__ con `sqlacodegen` para saber cual es el modelo(s) de una base de datos desde el archivo `.db` Instalar el paquede `sqlacodegen`<jupyter_code># https://pypi.org/project/sqlacodegen/<jupyter_output><empty_output><jupyter_text>SQLAlchemy & Pandas<jupyter_code>!sqlacodegen sqlite:///app_test.db
# SE COPIA EL MODELO PARA PODER OTRA BD CON OTRA SESION
engine = create_engine("sqlite:///app_test.db")
Session = sessionmaker(bind=engine)
session = Session()
# coding: utf-8
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class MovieApp2(Base):
    __tablename__ = 'movies'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    genre = Column(String)
    description = Column(String)
    year = Column(Integer)
    
    def __repr__(self):
        return f"<Movie name={self.name}>"
qs = session.query(MovieApp2).all()
print(qs)
# SABER QUE ES LO QUE CONTIENE EL QUERY SET ANTERIOR
for old_obj in qs:
    stored_data_dict = old_obj.__dict__
    print(stored_data_dict)
# COPIAR LOS DATOS DE UNA BASE DE DATOS A OTRA BD CON sqlacodegen
for old_obj in qs:
    movie_obj = Movie(name=old_obj.name, genre=old_obj.genre, year=old_obj.year)
    print(movie_obj.name)
    my_sess.add(movie_obj) # ES LA SESSION QUE ESTA ARRIBA CON LA BD DE LA TABLA Movies Y SE INSERTA A MoviesApp2
my_sess.commit()
# HACER LO CON pandas
# MOSTRAR CON PANDAS UNA(ANTERIOR) BD DE SQLITE
old_engine = create_engine("sqlite:///app_test.db")
old_df = pd.read_sql_table("movies", old_engine)
old_df.head()
# MOSTRAR CON PANDAS UNA(NUEVA) BD DE SQLITE
current_engine = create_engine("sqlite:///app2.db")
current_df = pd.read_sql_table("movies", current_engine)
current_df.head()
# CONCATENAR LAS DOS BD A 20
final_df = pd.concat([current_df, old_df])
final_df.head(n=20)
# RE-ORGANIZAR EL INDEX DE LA CONCATENACIÓN DE LAS DOS BD A 20
final_df = final_df[['name', 'genre', 'description', 'year']]
final_df.reset_index(inplace=True, drop=True) # EYE
final_df.head(n=20)
# AGREGAR UNA NUEVA TABLA A LA BD app2.db CON current_engine SI EXISTE SE REMPLASA
final_df.to_sql(
    "movies_2",
    current_engine,
    if_exists='replace',
    dtype = {
        "name": String,
        "genre": String,
        "year": Integer,
        "description": String,
    }
)
!sqlacodegen sqlite:///app2.db
# CON sqlite3 HACER LA CONECCION Y LEER PANDAS Y SQL LA BD
import sqlite3

conx = sqlite3.connect("app_test.db")  # SE PUEDE DE ESTA FORMA
conx2 = create_engine("sqlite:///app_test.db")  # TAMBIEN SE PUEDE CON create_engine
df = pd.read_sql_query("SELECT * FROM movies", conx)
df.head()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup