表设计

表:student

StdIDint学生ID
StdNamevarchar(100)学生姓名
Genderenum('M','F')性别
Ageint年龄

表:Course

CouIDint课程ID
CNamevarchar(100)课程名称
TIDint老师ID

表:Score

SIDint分数ID
StdDIint学生ID
CouIDint课程ID
Gradeint分数

表:teacher

TIDint老师ID
Tnamevarchar(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>
05-11 14:07