Python中MySQL表的建立，数据的插入及查询

2019独角兽企业重金招聘Python工程师标准>>>

MySQL表的建立

例子：

import MySQLdb

def connect_mysql():

    db_config = {

        'host': '192.168.48.128',

        'port': 3306,

        'user': 'xiang',

        'passwd': '123456',

        'db': 'python',

        'charset': 'utf8'

    }

    cnx = MySQLdb.connect(**db_config)

    return cnx

 

if __name__ == '__main__':

    cnx = connect_mysql()

    cus = cnx.cursor()

    # sql  = '''insert into student(id, name, age, gender, score) values ('1001', 'ling', 29, 'M', 88), ('1002', 'ajing', 29, 'M', 90), ('1003', 'xiang', 33, 'M', 87);'''

    student = '''create table Student(

            StdID int not null,

            StdName varchar(100) not null,

            Gender enum('M', 'F'),

            Age tinyint

    )'''

    course = '''create table Course(

            CouID int not null,

            CName varchar(50) not null,

            TID int not null

    )'''

    score = '''create table Score(

                SID int not null,

                StdID int not null,

                CID int not null,

                Grade int not null

        )'''

    teacher = '''create table Teacher(

                    TID int not null,

                    TName varchar(100) not null

            )'''

 tmp = '''set @i := 0;

            #mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。set这个是mysql中设置变量的特殊用法，当@i需要在select中使用的时候，必须加:

            create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;

        '''

    try:

        cus.execute(student)

        cus.execute(course)

        cus.execute(score)

        cus.execute(thearch)

    cus.execute(tmp)

        cus.close()

        cnx.commit()

    except Exception as e:

        cnx.rollback()

        print('error')

        raise e

    finally:

        cnx.close()

 

MySQL数据的建立

例子:

import MySQLdb

def connect_mysql():

    db_config = {

        'host': '192.168.48.128',

        'port': 3306,

        'user': 'xiang',

        'passwd': '123456',

        'db': 'python',

        'charset': 'utf8'

    }

    cnx = MySQLdb.connect(**db_config)

    return cnx

 

if __name__ == '__main__':

    cnx = connect_mysql()

    students = '''set @i := 10000;

            #floor()函数表示去尾法取整；rand()函数代表的是从0到1取一个随机的小数；3+floor(rand() * 75)就代表的是：3到77的任意一个数字；concat()函数是一个对多个字符串拼接函数；sha1()是一个加密函数，sha1(rand())对生成的0到1的一个随机小数进行加密，转换成字符串的形式；

            insert into Student select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75)), case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end, 25-floor(rand() * 5)  from tmp a, tmp b, tmp c, tmp d;

        '''

    course = '''set @i := 10;

            insert into Course select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 40)),  1 + floor(rand() * 100) from tmp a;

        '''

    score = '''set @i := 10000;

            insert into Score select @i := @i +1, floor(10001 + rand()*10000), floor(11 + rand()*10), floor(1+rand()*100) from tmp a, tmp b, tmp c, tmp d;

        '''

    theacher = '''set @i := 100;

            insert into Teacher select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;

        '''

    try:

        cus_students = cnx.cursor()

        cus_students.execute(students)

        cus_students.close()

 

        cus_course = cnx.cursor()

        cus_course.execute(course)

        cus_course.close()

 

        cus_score = cnx.cursor()

        cus_score.execute(score)

        cus_score.close()

 

        cus_teacher = cnx.cursor()

        cus_teacher.execute(theacher)

        cus_teacher.close()

 

        cnx.commit()

    except Exception as e:

        cnx.rollback()

        print('error')

        raise e

    finally:

        cnx.close()

 

 

MySQL数据的查询

例子：

import codecs

import MySQLdb

def connect_mysql():

    db_config = {

        'host': '192.168.48.128',

        'port': 3306,

        'user': 'xiang',

        'passwd': '123456',

        'db': 'python',

        'charset': 'utf8'

    }

    cnx = MySQLdb.connect(**db_config)

    return cnx

 

if __name__ == '__main__':

    cnx = connect_mysql()

    #()中的sql语句是把所有学生名字重复的学生都列出来；此sql是关联查询

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

转载于:https://my.oschina.net/u/3713142/blog/1577664