sqlalchemy基本使用

使用python向数据库插入数据时候，写原生sql是很痛苦的。。。
使用sqlalchemy， 一能兼容多种数据库，二来insert很简单。

create_table.py

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey

# dialect+driver://username:password@host:port/database
# sqlite: 'sqlite:///foo.db'
# sqlite_mem: 'sqlite://'
# mysql: 'mysql+pymysql://scott:tiger@localhost/foo'

engine = create_engine('postgresql+psycopg2://postgres:hua123@localhost:5432/test')
metadata = MetaData()

user = Table('users', metadata,
             Column('id', Integer, primary_key=True),
             Column('name', String),
             Column('full_name', String))

address = Table('address', metadata,
                Column('id', Integer, primary_key=True),
                Column('user_id', None, ForeignKey('users.id')),
                Column('email_address', String, nullable=False))


if __name__ == "__main__":
    metadata.create_all(engine)

run.py

from create_table import user, address, engine

conn = engine.connect()

# 插入数据
conn.execute(user.insert(), name='xiaoming', full_name='wangxiaoming')
conn.execute(user.insert(), [{'name': 'hongzhi', 'full_name': 'lihongzhi'},
                             {'name': 'gang', 'full_name': 'wanggang'}])

# 其他语句
with engine.begin() as conn:
    for line in conn.execute('select * from users;'):
        print(line)