ahtornado<em></e 阅读(38) 评论(0)

在操作设计数据库之前,我们先要设计数据库表结构,我们就来分析分析经典的学生,课程,成绩,老师这几者他们之间的关系,我们先来分析各个主体他们直接有什么属性,并确定表结构,在实际开发过程中,根据自己的业务需要和属性,设计不同的表结构,以下是我设计的表结构:
Student

Course

Score

Teacher
有了表结构,下面我们就来创建表吧

点击(此处)折叠或打开

  1. #!/usr/bin/env python
  2. # -*- coding:utf-8 -*-
  3. # Author :Alvin.xie
  4. # @Time :2017-11-22 14:37
  5. # @file :maketable.py

  6. import MySQLdb

  7. def connect_mysql():
  8.     db_config = {
  9.         'host': '10.89.1.10',
  10.         'port': 3306,
  11.         'user': 'demo',
  12.         'passwd': '123qaz',
  13.         'db': 'python_test',
  14.         'charset': 'utf8'
  15.     }
  16.     cnx = MySQLdb.connect(**db_config)
  17.     return cnx


  18. if __name__ == '__main__':
  19.     cnx = connect_mysql()
  20.     cus = cnx.cursor()


  21. student = '''create table Student(
  22.         StdID int not null,
  23.         StdName varchar(100) not null,
  24.         Gender enum('M', 'F'),
  25.         Age tinyint
  26. )'''


  27. course = '''create table Course(
  28.         CouID int not null,
  29.         CName varchar(50) not null,
  30.         TID int not null
  31. )'''


  32. score = '''create table Score(
  33.             SID int not null,
  34.             StdID int not null,
  35.             CID int not null,
  36.             Grade int not null
  37. )'''


  38. teacher = '''create table Teacher(
  39.                 TID int not null,
  40.                 TName varchar(100) not null
  41. )'''


  42. tmp = '''set @i := 0;
  43.         create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;
  44.     '''
  45. try:
  46.     cus.execute(student)
  47.     cus.execute(course)
  48.     cus.execute(score)
  49.     cus.execute(teacher)
  50.     cus.execute(tmp)
  51.     cus.close()
  52.     cnx.commit()
  53. except Exception as e:
  54.     cnx.rollback()
  55.     print('error')
  56.     raise e
  57. finally:
  58.     cnx.close()

没有任何异常,在数据库中查看表,出现这五个表。说明这五个表已经创建成功。

执行结果: