create table

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time   : 2017/11/22 23:04
# @Author : lijunjiang
# @File   : Creater-tables.py
import MySQLdb

# sql 语句

#创建student表
Student = '''
    create table Student(
        StdID int primary key not null,
        StdName varchar(100) not null,
        Gender enum('M','F') not null,
        Age int 
    )
'''

# 创建 Course 表
Course = '''
    create table Course(
        CouID int primary key not null,
        CName varchar(100) not null,
        TID int not null
    )
'''

# 创建 Score 表
Score= '''
    create table Score(
        SID int primary key not null,
        StdID int not null,
        CouID int not null,
        Grade int not null
    )
'''

# 创建Teacher 表
Teacher='''
    create table Teacher(
        TID int primary key not null,
        TName varchar(100) not null
    )
'''
 # 创建TMP 表

TMP='''
    set @i :=0;
    create table TMP as select (@i := @i + 1) as id from information_schema.tables limit 10;
'''

def connect_mysql():
    db_config = dict(host="11.11.11.11", port=3306, db="python", charset="utf8", user="python", passwd="python")
    try:
        cnx = MySQLdb.connect(**db_config)
    except Exception as err:
        raise err
    return cnx

if __name__ == "__main__":
    sql = "create table test(id int not null);"
    cnx = connect_mysql()  # 连接mysql
    cus = cnx.cursor()     # 创建一个游标对象    try:
    try:
        cus.execute(Student)
        cus.execute(Course)
        cus.execute(Score)
        cus.execute(Teacher)
        cus.execute(TMP)
        cus.close()
        cnx.commit()
    except Exception as err:
        cnx.rollback()
        raise err
    finally:
        cnx.close()