Back Home

Ineteractive Console

pip install sqlalchemy_console

Models and Types

Class name Python type SQL type (for SQLite driver)
String string TEXT or VARCHAR
Integer int INTEGER
SmallInteger int SMALLINT
Numeric float, Decimal NUMERIC
Float(Numeric) float NUMERIC
DateTime datetime.datetime TIMESTAMP
Date datetime.date DATE
Time datetime.time TIME
Binary byte string BLOB
Boolean bool BOOLEAN
Unicode unicode TEXT or VARCHAR
PickleType any object that can be pickled BLOB
FLOAT(Numeric) float, Decimal NUMERIC
TEXT(String) string TEXT
DECIMAL(Numeric) float, Decimal NUMERIC
INT, INTEGER(Integer) int INTEGER
TIMESTAMP(DateTime) datetime.datetime TIMESTAMP
DATETIME(DateTime) datetime.datetime TIMESTAMP
CLOB(String) string TEXT
VARCHAR(String) string VARCHAR or TEXT
CHAR(String) string CHAR or TEXT
NCHAR(Unicode) string VARCHAR, NCHAR, or TEXT
BLOB(Binary) byte string BLOB
BOOLEAN(Boolean) bool BOOLEAN

https://www.oreilly.com/library/view/essential-sqlalchemy/9780596516147/ch04.html

With Postgres Specific types being:

Class name Python type SQL type
PGArray any TypeEngine type engine[]
PGBigInteger int, long BIGINT
PGInet none INET
PGInterval none INTERVAL

PostgreSQL dialect supports both JSON and JSONB datatypes, including psycopg2’s native support and support for all of PostgreSQL’s special operators:

  • postgresql.JSON
  • postgresql.JSONB

https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html

Creating Tables with Inheritance

def createTableInherit():
    engine = create_engine(
        'postgresql://postgres:xxxxx@localhost:5432/postgres', echo=True)
    meta = MetaData()
 
    people = Table('people', meta,
        Column('id', Integer, primary_key=True)
    )
 
    engineer =  Table('engineer', meta,
        Column('language', String(50)),
        postgresql_inherits="people"
    )
 
    meta.create_all(engine)

Altering a Table

def alterTable():
    engine = create_engine(
        'postgresql://postgres:xxxxx@localhost:5432/postgres', echo=True)
    engine.execute('ALTER TABLE public.etf ADD "Name" varchar(10) NULL')

Manual Insert

# Create connection
conn = engine.connect()
# Begin transaction
trans = conn.begin()
conn.execute('INSERT INTO "EX1" (name) '
             'VALUES ("Hello")')
trans.commit()

ORM

ORM Meta data Create

# Create a metadata instance
metadata = MetaData(engine)
# Declare a table
table = Table('Example',metadata,
              Column('id',Integer, primary_key=True),
              Column('name',String))
# Create all tables
metadata.create_all()

ORM Working with Existing Tables

from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table
 
db_uri = 'sqlite:///db.sqlite'
engine = create_engine(db_uri)
 
# Create MetaData instance
metadata = MetaData(engine, reflect=True)
print(metadata.tables)
 
# Get Table
ex_table = metadata.tables['Example']
print(ex_table)

Mapping dynamically

metadata = MetaData()
 
# This tuple of columns could be generated programmaticly
columns = (
    Column('design_name', String(80), primary_key=True),
    Column('user_name', String(80), nullable=False),
    Column('rev_tag', String(80), nullable=False),
    ...
)
 
designs = Table('designs', metadata, *columns)
 
class Designs(object):
    def __init__(self, json_data):    
      for key, value in data.iteritems():
        setattr(self, key, value)
 
mapper(Designs, designs)

Mapping Json to Table Class

if the key's match the table/object fiends then you can

user = User(**obj)

if you have in your json fields that don't belong to that model you can filter them out with dict comprehension

user = User(**{k:v for k, v in obj.items() if k in {'id', 'name'}})
 
sqlalchemy_notes.txt · Last modified: 2019/07/24 01:45 by root
 
RSS - 200 © CrosswireDigitialMedia Ltd