<jupyter_start><jupyter_text>Storing Data with Pickles<jupyter_code>import pickle
import pandas as pd
data = [{"name": "Justin"}]

with open("users.pkl", 'wb') as f:
    pickle.dump(data, f) # pickle.dump
data_from_pkl = pickle.load(open('users.pkl', 'rb')) # pickle.loads -> pickle.load
print(data_from_pkl, type(data_from_pkl))
df = pd.DataFrame(data)
df.head()
df.to_pickle("df_pkl")
df.dtypes
df2 = pd.read_pickle("df_pkl")
df2.head()
df2.dtypes
df.to_csv("my_df.csv", index=False)<jupyter_output><empty_output><jupyter_text>SQLAlchemySQLAlchmey & Django have a similar object-relational mappings (aka `ORM`) are ways to connect Python to a SQL database.<jupyter_code>from dataclasses import dataclass

@dataclass
class Movie:
    name:str = 'Unknown'
    genre:str = 'Action'
    year:int = None
    
# class Movie:
#     name = 'Unknown'
#     genre = 'Action'
    
#     def __init__(self, name='', *args, **kwargs):
#         super().__init__(*args, **kwargs)
#         self.name = name
movie_obj = Movie(name='Interstellar', genre='Sci-Fi')
movie_obj.name
data = [{
    "name": "Interstellar",
    "genre": "Sci-Fi"
},
{
    "name": "The Martian",
    "genre": "Sci-Fi"
},
{
    "name": "Arrival",
    "genre": "Sci-Fi"
}
]
df = pd.DataFrame(data)
df.head()
import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
engine = create_engine("sqlite:///app.db") # mysql, postgres
Session = sessionmaker(bind=engine)
my_sess = Session()
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Movie(Base): # table
    __tablename__ = "movies"
    
    id = Column(Integer, primary_key=True) # auto created for us
    name = Column(String) # 'Unknown' # col
    genre = Column(String) # 'Action' # col
    description = Column(String) # 'Action' # col
    year = Column(Integer, nullable=True) # None # col
    
    
    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)
movie_obj.id
my_sess.add(movie_obj) # prepare to save
my_sess.commit() # save
print(movie_obj.id, movie_obj.description)
movie_obj2 = Movie(name='The Martian', genre='Sci-Fi')
print(movie_obj2.name, movie_obj2.id)
my_sess.add(movie_obj2)
movie_obj3 = Movie(name='Inception', genre='Sci-Fi')
print(movie_obj3.name, movie_obj3.id)
my_sess.add(movie_obj3)
my_sess.commit()
print(movie_obj2.id, movie_obj3.id)<jupyter_output>8 9<jupyter_text>CRUD in SQLAlchemy- **C**reate- **R**etreive- **U**pdate- **D**elete<jupyter_code>Session = sessionmaker(bind=engine)
session = Session()
# Create
movie = Movie(name='Guaridans of the Galaxy')
session.add(movie)
session.commit()
# Retreive

# Get 1 item
movie_a = session.query(Movie).get(5)
print(movie_a.id, movie_a.name, movie_a.description)
# List
qs = session.query(Movie).all()
print(qs)
# List & Filter by Column Value
qs = session.query(Movie).filter_by(name='Guardians of the Galaxy').all()
qs
# List & Filter by Column Value Containing Something
qs = session.query(Movie).filter(Movie.name.contains("Guaridans")).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(5)
movie_a.description = "A dream within a dream"
print(movie_a.id, movie_a.name, movie_a.description)
session.commit()
movie_a = session.query(Movie).get(5)
print(movie_a.description)
qs = session.query(Movie).filter(Movie.name.contains("Guaridans")).all()
for movie_obj in qs:
    movie_obj.name = "Guardians of the Galaxy"
session.commit()
qs = session.query(Movie).filter(Movie.name.contains("Guaridans")).all()
qs
# Delete
movie_a = session.query(Movie).get(1)
session.delete(movie_a)
session.commit()
session.flush()<jupyter_output><empty_output><jupyter_text>SQLAlchemy & Pandas<jupyter_code>!sqlacodegen sqlite:///app2.db
engine = create_engine("sqlite:///app2.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)
    year = Column(Integer)
    
    def __repr__(self):
        return f"<Movie name={self.name}>"
qs = session.query(MovieApp2).all()
print(qs)
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)
my_sess.commit()
old_engine = create_engine("sqlite:///app2.db")
old_df = pd.read_sql_table("movies", old_engine)
old_df.head()
current_engine = create_engine("sqlite:///app.db")
current_df = pd.read_sql_table("movies", current_engine)
current_df.head()
final_df = pd.concat([current_df, old_df])
final_df.head(n=20)
final_df = final_df[['name', 'genre', 'description', 'year']]
final_df.reset_index(inplace=True, drop=True)
final_df.head(n=20)
final_df.to_sql(
    "movies",
    current_engine,
    if_exists='replace',
    dtype = {
        "name": String,
        "genre": String,
        "year": Integer,
        "description": String,
    }
)
!sqlacodegen sqlite:///app.db
import sqlite3


conx = sqlite3.connect('app.db')
conx2 = create_engine("sqlite:///app.db")

df = pd.read_sql_query("SELECT * FROM movies", conx2)
df.head()
# cfe.sh/youtube<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14845791949817042, 'fraction_numerical': 0.005924377069175815, 'mean_word_length': 5.010471204188482, 'pattern_counts': {'":': 11, '<': 18, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '632393', 'license': 'MIT', 'n_tokens_mistral': 2159, 'n_tokens_neox': 2089, 'n_words': 422, 'path': 'tutorial-reference/Day 26/Day 26 - Python to Database via SQL.ipynb', 'repo_name': 'Rohianon/30-Days-of-Python', 'size': '5739'}