起步
"""sqlalchemy创建表
四张表:业务线,服务,用户,角色,利用ORM创建出它们,并建立好它们直接的关系
"""
from sqlalchemy import create_engine
from sqlalchemy. ext. declarative import declarative_base
from sqlalchemy. orm import sessionmaker
from sqlalchemy. sql import func
from sqlalchemy import Column
from sqlalchemy import Boolean
from sqlalchemy import Integer
from sqlalchemy import BigInteger
from sqlalchemy import SmallInteger
from sqlalchemy import Float
from sqlalchemy import DECIMAL
from sqlalchemy import CHAR
from sqlalchemy import VARCHAR
from sqlalchemy import String
from sqlalchemy import Text
from sqlalchemy import Time
from sqlalchemy import Date
from sqlalchemy import DateTime
from sqlalchemy import TIMESTAMP
from sqlalchemy import Enum
from sqlalchemy import JSON
from sqlalchemy import CheckConstraint
from sqlalchemy import ForeignKey
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import PrimaryKeyConstraint
from sqlalchemy import UniqueConstraint
from sqlalchemy import Index
Base = declarative_base( )
engine = create_engine( 'mysql+pymysql://root:123@localhost/hardy4_db?charset=utf8mb4' , max_overflow= 10 , echo= True )
创建单表
class Business ( Base) :
__tablename__ = 'business'
id = Column( Integer, nullable= False , primary_key= True , autoincrement= True )
ident = Column( Integer, nullable= False , unique= True )
bname = Column( String( length= 32 ) , nullable= False , index= True )
bname2 = Column( CHAR( length= 32 ) , nullable= False , index= True )
price = Column( Float( precision= 10 ) )
money = Column( DECIMAL( precision= 10 , scale= 4 ) )
language = Column( Enum( 'python3' , 'django2.2.1' , 'flask' ) )
create_date = Column( Date)
create_time = Column( Time)
create_datetime = Column( DateTime( timezone= True ) , server_default= func. now( ) , onupdate= func. now( ) )
create_timestamp = Column( TIMESTAMP( timezone= True ) , server_default= func. now( ) , onupdate= func. now( ) )
content_text = Column( Text)
status = Column( Boolean)
jdoc = Column( JSON)
一对多 or 多对一
class Service ( Base) :
__tablename__ = 'service'
id = Column( Integer, nullable= False , primary_key= True , autoincrement= True )
sname = Column( String( 32 ) , nullable= False , index= True )
sname2 = Column( VARCHAR( 32 ) , nullable= False , index= True )
ip = Column( String( 15 ) , nullable= False )
port = Column( Integer, nullable= False )
business_id = Column( Integer, ForeignKey( column= 'business.id' , onupdate= 'CASCADE' , ondelete= 'CASCADE' ) )
__table_args__ = (
UniqueConstraint( ip, port, name= 'uix_ip_port' ) ,
Index( 'ix_id_sname' , id , sname) ,
CheckConstraint( sqltext= 'port > 8080' , name= 'service_chk_1' )
)
一对一
class Role ( Base) :
__tablename__ = 'role'
id = Column( Integer, primary_key= True , autoincrement= True )
rname = Column( String( 32 ) , nullable= False , index= True )
priv = Column( String( 64 ) , nullable= False )
business_id = Column( Integer, ForeignKey( column= 'business.id' , name= 'fk_role_to_bid' ) , unique= True )
多对多
class Users ( Base) :
__tablename__ = 'users'
id = Column( Integer, autoincrement= True )
uname = Column( String( 32 ) , nullable= False , index= True )
__table_args__ = (
PrimaryKeyConstraint( id , uname, name= 'pk_id_uname' ) ,
)
class Users2Role ( Base) :
__tablename__ = 'users2role'
id = Column( Integer, primary_key= True , autoincrement= True )
uid = Column( Integer, ForeignKey( column= 'users.id' ) )
rid = Column( Integer, ForeignKey( column= 'role.id' ) )
__table_args = (
UniqueConstraint( uid, rid, name= 'uix_uid_rid' ) ,
)
联合外键
class Host100 ( Base) :
__tablename__ = 'host100'
id = Column( Integer, autoincrement= True , unique= True )
ip = Column( String( 32 ) )
port = Column( Integer)
__table_args__ = (
PrimaryKeyConstraint( ip, port) ,
)
class Host200 ( Base) :
__tablename__ = 'host200'
id = Column( Integer, primary_key= True , autoincrement= True )
ip = Column( String( 32 ) )
port = Column( Integer)
__table_args__ = (
ForeignKeyConstraint( columns= ( ip, port) , refcolumns= ( 'host100.ip' , 'host100.port' ) ) ,
)
初始化表
Base. metadata. create_all( bind= engine)
删除表
Base. metadata. drop_all( bind= engine)
整体代码
"""sqlalchemy创建表
四张表:业务线,服务,用户,角色,利用ORM创建出它们,并建立好它们直接的关系
"""
from sqlalchemy import create_engine
from sqlalchemy. ext. declarative import declarative_base
from sqlalchemy. orm import sessionmaker
from sqlalchemy. sql import func
from sqlalchemy import Column
from sqlalchemy import Boolean
from sqlalchemy import Integer
from sqlalchemy import BigInteger
from sqlalchemy import SmallInteger
from sqlalchemy import Float
from sqlalchemy import DECIMAL
from sqlalchemy import CHAR
from sqlalchemy import VARCHAR
from sqlalchemy import String
from sqlalchemy import Text
from sqlalchemy import Time
from sqlalchemy import Date
from sqlalchemy import DateTime
from sqlalchemy import TIMESTAMP
from sqlalchemy import Enum
from sqlalchemy import JSON
from sqlalchemy import CheckConstraint
from sqlalchemy import ForeignKey
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import PrimaryKeyConstraint
from sqlalchemy import UniqueConstraint
from sqlalchemy import Index
Base = declarative_base( )
engine = create_engine( 'mysql+pymysql://root:123@localhost/hardy4_db?charset=utf8mb4' , max_overflow= 10 , echo= True )
class Business ( Base) :
__tablename__ = 'business'
id = Column( Integer, nullable= False , primary_key= True , autoincrement= True )
ident = Column( Integer, nullable= False , unique= True )
bname = Column( String( length= 32 ) , nullable= False , index= True )
bname2 = Column( CHAR( length= 32 ) , nullable= False , index= True )
price = Column( Float( precision= 10 ) )
money = Column( DECIMAL( precision= 10 , scale= 4 ) )
language = Column( Enum( 'python3' , 'django2.2.1' , 'flask' ) )
create_date = Column( Date)
create_time = Column( Time)
create_datetime = Column( DateTime( timezone= True ) , server_default= func. now( ) , onupdate= func. now( ) )
create_timestamp = Column( TIMESTAMP( timezone= True ) , server_default= func. now( ) , onupdate= func. now( ) )
content_text = Column( Text)
status = Column( Boolean)
jdoc = Column( JSON)
class Service ( Base) :
__tablename__ = 'service'
id = Column( Integer, nullable= False , primary_key= True , autoincrement= True )
sname = Column( String( 32 ) , nullable= False , index= True )
sname2 = Column( VARCHAR( 32 ) , nullable= False , index= True )
ip = Column( String( 15 ) , nullable= False )
port = Column( Integer, nullable= False )
business_id = Column( Integer, ForeignKey( column= 'business.id' , onupdate= 'CASCADE' , ondelete= 'CASCADE' ) )
__table_args__ = (
UniqueConstraint( ip, port, name= 'uix_ip_port' ) ,
Index( 'ix_id_sname' , id , sname) ,
CheckConstraint( sqltext= 'port > 8080' , name= 'service_chk_1' )
)
class Role ( Base) :
__tablename__ = 'role'
id = Column( Integer, primary_key= True , autoincrement= True )
rname = Column( String( 32 ) , nullable= False , index= True )
priv = Column( String( 64 ) , nullable= False )
business_id = Column( Integer, ForeignKey( column= 'business.id' , name= 'fk_role_to_bid' ) , unique= True )
class Users ( Base) :
__tablename__ = 'users'
id = Column( Integer, autoincrement= True )
uname = Column( String( 32 ) , nullable= False , index= True )
__table_args__ = (
PrimaryKeyConstraint( id , uname, name= 'pk_id_uname' ) ,
)
class Users2Role ( Base) :
__tablename__ = 'users2role'
id = Column( Integer, primary_key= True , autoincrement= True )
uid = Column( Integer, ForeignKey( column= 'users.id' ) )
rid = Column( Integer, ForeignKey( column= 'role.id' ) )
__table_args = (
UniqueConstraint( uid, rid, name= 'uix_uid_rid' ) ,
)
class Host100 ( Base) :
__tablename__ = 'host100'
id = Column( Integer, autoincrement= True , unique= True )
ip = Column( String( 32 ) )
port = Column( Integer)
__table_args__ = (
PrimaryKeyConstraint( ip, port) ,
)
class Host200 ( Base) :
__tablename__ = 'host200'
id = Column( Integer, primary_key= True , autoincrement= True )
ip = Column( String( 32 ) )
port = Column( Integer)
__table_args__ = (
ForeignKeyConstraint( columns= ( ip, port) , refcolumns= ( 'host100.ip' , 'host100.port' ) ) ,
)
def init_db ( ) :
Base. metadata. create_all( bind= engine)
def drop_db ( ) :
Base. metadata. drop_all( bind= engine)
if __name__ == '__main__' :
init_db( )