python mysql创建表

表设计

表：student

StdID	int	否	是	学生ID
StdName	varchar(100)	否		学生姓名
Gender	enum('M','F')	是		性别
Age	int	是		年龄

表：Course

CouID	int	否	是	课程ID
CName	varchar(100)	否		课程名称
TID	int	否		老师ID

表：Score

SID	int	否	是	分数ID
StdDI	int	否		学生ID
CouID	int	否		课程ID
Grade	int	否		分数

表：teacher

TID	int	否	是	老师ID
Tname	varchar(100)	否	是	老师姓名

创建表

#!/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()

查看

mysql> use python;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+------------------+

| Tables_in_python |

+------------------+

| Course           |

| Score            |

| Student          |

| TMP              |

| Teacher          |

+------------------+

5 rows in set (0.00 sec)

mysql> select * from Student;

Empty set (0.00 sec)

mysql> show columns from Student;

+---------+---------------+------+-----+---------+-------+

| Field   | Type          | Null | Key | Default | Extra |

+---------+---------------+------+-----+---------+-------+

| StdID   | int(11)       | NO   | PRI | NULL    |       |

| StdName | varchar(100)  | NO   |     | NULL    |       |

| Gender  | enum('M','F') | NO   |     | NULL    |       |

| Age     | int(11)       | YES  |     | NULL    |       |

+---------+---------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> show columns from Course;

+-------+--------------+------+-----+---------+-------+

| Field | Type         | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| CouID | int(11)      | NO   | PRI | NULL    |       |

| CName | varchar(100) | NO   |     | NULL    |       |

| TID   | int(11)      | NO   |     | NULL    |       |

+-------+--------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> show columns from Score;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| SID   | int(11) | NO   | PRI | NULL    |       |

| StdID | int(11) | NO   |     | NULL    |       |

| CouID | int(11) | NO   |     | NULL    |       |

| Grade | int(11) | NO   |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> show columns from Teacher;

+-------+--------------+------+-----+---------+-------+

| Field | Type         | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| TID   | int(11)      | NO   | PRI | NULL    |       |

| TName | varchar(100) | NO   |     | NULL    |       |

+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> show columns from TMP;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| id    | bigint(21) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> select * from TMP;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

|    6 |

|    7 |

|    8 |

|    9 |

|   10 |

+------+

10 rows in set (0.00 sec)

mysql>