Python对mysql的操作三

1）创建表

import MySQLdbdef connect_mysql():    db_config = {        "host":"localhost",        "user":"root",        "passwd":"root",        "db":"python_db",        "charset":"utf8"    }    try:        cnx = MySQLdb.connect(**db_config)    except Exception as e:        raise e    return cnxstudent = '''    create table student(        stdid int primary key not null,        stdname varchar(100) not null,        gender enum('F','M'),        age int    )'''course = '''    create table course(        couid int primary key not null,        cname varchar(100) not null,        tid int not null    )'''sorce = '''    create table sorce(        sid int primary key not null,        std int not null,        couid int not null,        grade int not null    )'''teacher = '''    create table teacher(        tid int primary key not null,        tname varchar(100) not null    )'''tmp = '''    set @a := 0;  # mysql中变量不用事前申明，直接用“set @变量名”就可以了    create table tmp as select (@a := @a + 1)as id from information_schema.tables limit 10;    # 当@a需要在select中使用的时候，必须加:'''if __name__ == '__main__':    cnx = connect_mysql()    cus = cnx.cursor()    try:        cus.execute(student)        cus.execute(course)        cus.execute(sorce)        cus.execute(teacher)        cus.execute(tmp)        cus.close()        cnx.commit()    except Exception as e:        cnx.rollback()        raise e    finally:        cnx.close()

2）增加数据

# select * from tmp;  # 10条数据# select * from tmp a,tmp b,tmp c;  # 1000条数据，相当于10*10*10# 获得随机字符串的设计# 增加的数据是随机数据， rand()函数随机生成一个0-1的随机数# sha1()函数对数字进行加密，然后生成一堆字符串# concat()是拼接多个字符串的函数# substr()截取多少个字符# 获得随机整数的设计# rand()*50   获得0-50的随机小数# floor() 这个函数代表的是去尾法取整数# 男女的设计# rand()*10  /2   最后取余数# 如果余数为1，就设置为M# 如果余数为0，就设置为Fstudent1 = '''    set @b := 10000;    insert into student select @b:= @b+1,substr(concat(sha1(rand()),sha1(rand())),1,5+floor(rand()*50)),case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,20+floor(rand()*8) from tmp a,tmp b,tmp c,tmp d;'''course1 = '''    set @c := 10;    insert into course select @c:= @c+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 45)),  100 + floor(rand() * 100) from tmp a;'''sorce1 = '''    set @d := 10000;    insert into sorce select @d:= @d+1, floor(10001 + rand()*10000), floor(11 + rand()*10), floor(1+rand()*100) from tmp a, tmp b, tmp c, tmp d;'''theacher1 = '''    set @e := 100;    insert into teacher select @e:= @e+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;'''

3）查询数据

import codecsimport MySQLdbdef connect_mysql():    db_config = {        'host': 'localhost',        'port': 3306,        'user': 'root',        'passwd': 'root',        'db': 'python_db',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __name__ == '__main__':    cnx = connect_mysql()    sql = '''select * from course;'''    #sql = '''select * from student where stdname in (select stdname from student group by stdname having count(1)>1 ) order by stdname;'''    try:        cus = cnx.cursor()        cus.execute(sql)        result = cus.fetchall()        with codecs.open('select.txt', 'w+') as f:            for line in result:                f.write(str(line))                f.write('\n')        cus.close()        cnx.commit()    except Exception as e:        cnx.rollback()        print('error')        raise e    finally:        cnx.close()