mysqlalchmy操作之建表

1　创建链接基础类。

# -*- coding=utf-8 -*-
import os
from sqlalchemy import (create_engine,MetaData)
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from config.config import db_args

def get_engine_():
    args=db_args()
    password = os.getenv('DB_PASS', args['passwd'])
    charset = os.getenv('CharSet', args['charset'])
    connect_str= "{}+pymysql://{}:{}@{}:{}/{}?charset={}".format(args['db_type'], args['user'], password,args['host'], args['port'], args['db'],charset)
    engine=create_engine(connect_str)
    return engine
eng=get_engine_()
Base=declarative_base()#生成一个SqlORM 基类
Session=sessionmaker(bind=eng)#bind绑定,创建与数据库的会话session class
db_session=Session()
metadata=MetaData(get_engine_()) #

__all__ = ['eng', 'Base', 'db_session', 'metadata']  #它是一个string元素组成的list变量

　　2.设计需要的表的字段

# -*- coding=utf-8 -*-
from sqlalchemy import (
    Table, Column, INTEGER, String, Text,DateTime)
from .basic import metadata
import datetime
#user_info
user_info=Table("user_info",metadata,
                Column("id",INTEGER,primary_key=True,autoincrement=True),
                Column("login_id",String(200)),
                Column("login_pwd",String(200)),
                Column("user_name",String(200)),
                Column("domain_id",String(6), default=100505, server_default='100505'),
                Column("user_id",String(200)),
                Column("enable",String(2),default=1,server_default='1'),
                Column("need_comment", String(2), default=1, server_default='1'),
                Column("need_days", String(20), default=30, server_default='30'),
                Column("update_time",DateTime, default=datetime.datetime.utcnow,index=True),
                Column("create_user",String(200)),
    )

#home_info
home_info=Table("home_info",metadata,
                Column("id",INTEGER,primary_key=True,autoincrement=True),
                Column("user_id",String(200)),
                Column("user_name", String(200)),
                Column("follows_num",String(200),default=0, server_default='0'),
                Column("fans_num",String(200),default=0, server_default='0'),
                Column("wb_num",String(200),default=0, server_default='0'),
                Column("home_url", String(200)),
                Column("update_time", DateTime, default=datetime.datetime.utcnow,index=True),
                Column("create_user", String(200)),
                )
#wb_info
wb_info=Table("wb_info",metadata,
                Column("id",INTEGER,primary_key=True,autoincrement=True),
                Column("uid",String(200)),
                Column("wb_id", String(200)),
                Column("wb_content", String(2000)),
                Column("read_num",String(200),default=0, server_default='0'),
                Column("share_num",String(200),default=0, server_default='0'),
                Column("comment_num",String(200),default=0, server_default='0'),
                Column("like_num", String(200),default=0, server_default='0'),
                Column("wb_url", String(200)),
                Column("wb_time", DateTime, default=datetime.datetime.utcnow,index=True),
                Column("wb_device", String(200)),
                Column("update_time", DateTime, default=datetime.datetime.utcnow,index=True),
                Column("create_user", String(200)),
                )
#comment_info

comment_info=Table("comment_info",metadata,
                Column("id",INTEGER,primary_key=True,autoincrement=True),
                Column("wb_Id",String(200),index=True),
                Column("comment_user",String(200)),
                Column("comment_time",DateTime, default=datetime.datetime.utcnow,index=True),
                Column("comment_content",String(2000)),
                Column("comment_id", String(200)),
                Column("ico_url", String(200)),
                Column("update_time", DateTime, default=datetime.datetime.utcnow,index=True),
                Column("create_user", String(200)),
                )

__all__ = ['user_info', 'home_info', 'wb_info', 'comment_info']

　　3.映射到实体

# -*- coding=utf-8 -*-
from dbs.basic import Base
from dbs.tables import *
#user_info
#home_info
#wb_info
#comment_info

class LoginInfo(Base):
    __table__=user_info

class HomeoData(Base):
    __table__=home_info

class WeiBoData(Base):
    __table__=wb_info

class CommentInfo(Base):
    __table__=comment_info

　　4.创建表

# -*- coding=utf-8 -*-
from dbs.tables import *
from dbs.basic import metadata ,eng


def create_all_table():
    # 创建数据表，如果数据表存在，则忽视
    metadata.create_all()

if __name__ == "__main__":
    try:
        create_all_table()
        print("create table successful.")
    except:
        print("create table failed !!!")