表设计
表: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>