SqlAlchemy Python ORM
Tags: sql, sqlalchemy, orm
Published: 2021-05-31 03:42:55

-> Display the list of databases available
```
from sqlalchemy import create_engine
from pprint import pprint

db_user = 'debian-sys-maint'
db_passwd = 'WaVmczV8FotWsoAV'
db_host = 'localhost'

def main():
    engine = create_engine(f'mysql://{db_user}:{db_passwd}@{db_host}')
    pprint(engine.execute('SHOW DATABASES').fetchall())

if __name__ == '__main__':
    main()
```

-> Display the list of tables in a db
```
from sqlalchemy import create_engine
from pprint import pprint

db_user = 'debian-sys-maint'
db_passwd = 'WaVmczV8FotWsoAV'
db_host = 'localhost'

def main():
    engine = create_engine(f'mysql://{db_user}:{db_passwd}@{db_host}/{db}')
    pprint(engine.execute('SHOW TABLES').fetchall())

if __name__ == '__main__':
    main()
```

-> Add a Table
```
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.orm import relationship
from pprint import pprint


Base = declarative_base()


db_user = 'debian-sys-maint'
db_passwd = 'WaVmczV8FotWsoAV'
db = 'obs-parser'
db1 = 'mysql'
db_host = 'localhost'

class Sample(Base):
    __tablename__ = 'Sample'
    id = Column(String(50), primary_key=True)
    name = Column(String(50))
    age = Column(Integer)
    utc_timestamp = Column(DateTime)


def main():
    engine = create_engine(f'mysql://{db_user}:{db_passwd}@{db_host}/{db1}')
    pprint(engine.execute('SHOW TABLES').fetchall())
    Base.metadata.create_all(engine)
    pprint(engine.execute('SHOW TABLES').fetchall())


if __name__ == '__main__':
    main()
```

-> Delete a Table
```
engine.execute('DROP TABLE IF EXISTS <Table_name>')
```

-> Add and show Data from the Table Eg: JSON
```
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, JSON
from sqlalchemy.orm import relationship, sessionmaker
from pprint import pprint


Base = declarative_base()
db_user = 'root'
db_passwd = 'root'
db = 'mysql'
db_host = 'localhost'


#SQL Enigne
engine = create_engine(f'mysql://{db_user}:{db_passwd}@{db_host}/{db}', echo=True)


def add_data(class_name, data):
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()
    session.add(Sample(**data))
    session.commit()


def show_data(class_name):
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()
    for sample in session.query(class_name).order_by(class_name.id):
        pprint(vars(sample))


class Sample(Base):
    __tablename__ = 'Sample'


    id = Column(String(50), primary_key=True)
    name = Column(String(50))
    age = Column(Integer)
    utc_timestamp = Column(String(20))
    lacp_info = Column(JSON)



def main():
    engine.execute('DROP TABLE IF EXISTS Sample')
    Base.metadata.create_all(engine)
    #Add data
    data = {
        'id': 'f03bc258-8489-4cad-b5a5-b08d659c0f8b',
        'name': 'Sample',
        'age': 30,
        'utc_timestamp': '20181114_085010',
        'lacp_info': {
            'id': '60b89901-db40-408d-8a13-fe26d428a1dd',
            'lacp_key': '123'
        }
    }
    data1 = {
        'id': 'f03bc258-8489-4cad-b5a5-b08d659c0c3b',
        'name': 'Sample1',
        'age': 35,
        'utc_timestamp': '20181114_085012',
        'lacp_info': {
            'id': '60b89901-db40-408d-8a13-fe26d428a1ff',
            'lacp_key': '12345'
        }
    }
    add_data(Sample, data)
    add_data(Sample, data1)
    show_data(Sample)
    pprint(engine.execute('SHOW TABLES').fetchall())


if __name__ == '__main__':
    main()
```

-> Fetch values from a pre-existing table from a database
```
from sqlalchemy import create_engine, MetaData, select, Table
#Mysql config
mysql_db = 'obs3_meta'
mysql_user = 'obs3-meta'
mysql_password = 'obs3-meta'
mysql_host = 'localhost'

def get_engine():
    # SQL Enigne
    engine = create_engine(
        f'mysql://{mysql_user}:{mysql_password}@{mysql_host}/{mysql_db}', echo=False)
    return engine


def get_table_values(table=None):
    conn = get_engine().connect()
    select_st = select([table])
    return conn.execute(select_st)


if __name__ == '__main__':
    metadata = MetaData(get_engine(), reflect=True)
    games = Table('games', metadata, autoload=True)
    result = get_table_values(games )
    for r in result:
        print(r)
    # Getting value for a specific field
    conn = get_engine().connect()
    select_st = select([games]).where (games.c.name == 'NFS')
    for r in conn.execute(select_st):
        print(r)
```