Python对mysql的操作三

1）创建表

import MySQLdb
def 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 cnx
student = '''
    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，就设置为F

student1 = '''
    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 codecs
import MySQLdb
def connect_mysql():
    db_config = {
        'host': 'localhost',
        'port': 3306,
        'user': 'root',
        'passwd': 'root',
        'db': 'python_db',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __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()