#!/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'
),
)
# 一对多
class Favor(Base):
__tablename__ =
'favor'
nid =
Column
(
Integer
, primary_key=
True
)
caption =
Column
(String(50),
default
=
'red'
,
unique
=
True
)
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"
))
# 多对多
class
Group
(Base):
__tablename__ =
'group'
id =
Column
(
Integer
, primary_key=
True
)
name
=
Column
(String(64),
unique
=
True
, nullable=
False
)
port =
Column
(
Integer
,
default
=22)
class Server(Base):
__tablename__ =
'server'
id =
Column
(
Integer
, primary_key=
True
, autoincrement=
True
)
hostname =
Column
(String(64),
unique
=
True
, nullable=
False
)
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'
))
def init_db():
Base.metadata.create_all(engine)
def drop_db():
Base.metadata.drop_all(engine)