python pymysql_python操作pymysql—2

#!/usr/bin/env python

# -*- coding:utf-8 -*-

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

Base = declarative_base()

# 创建单表

class Users(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

name = Column(String(32))

extra = Column(String(16))

__table_args__ = (

UniqueConstraint('id', 'name', name='uix_id_name'),

Index('ix_id_name', 'name', 'extra'),

)

def __repr__(self):

return "%s-%s" %(self.id, self.name)

# 一对多

class Favor(Base):

__tablename__ = 'favor'

nid = Column(Integer, primary_key=True)

caption = Column(String(50), default='red', unique=True)

def __repr__(self):

return "%s-%s" %(self.nid, self.caption)

class Person(Base):

__tablename__ = 'person'

nid = Column(Integer, primary_key=True)

name = Column(String(32), index=True, nullable=True)

favor_id = Column(Integer, ForeignKey("favor.nid"))

# 与生成表结构无关，仅用于查询方便

favor = relationship("Favor", backref='pers')

# 多对多

class ServerToGroup(Base):

__tablename__ = 'servertogroup'

nid = Column(Integer, primary_key=True, autoincrement=True)

server_id = Column(Integer, ForeignKey('server.id'))

group_id = Column(Integer, ForeignKey('group.id'))

group = relationship("Group", backref='s2g')

server = relationship("Server", backref='s2g')

class Group(Base):

__tablename__ = 'group'

id = Column(Integer, primary_key=True)

name = Column(String(64), unique=True, nullable=False)

port = Column(Integer, default=22)

# group = relationship('Group',secondary=ServerToGroup,backref='host_list')

class Server(Base):

__tablename__ = 'server'

id = Column(Integer, primary_key=True, autoincrement=True)

hostname = Column(String(64), unique=True, nullable=False)

def init_db():

Base.metadata.create_all(engine)

def drop_db():

Base.metadata.drop_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

表结构 + 数据库连接