python-mysql增删改查

为什么80%的码农都做不了架构师？>>>   

创建表

 

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;
            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> show tables;

+------------------+

| Tables_in_python |

+------------------+

| Course           |

| Score            |

| Student          |

| Teacher          |

| tmp              |

+------------------+

3       rows in set (0.00 sec)

 

 

 

增加数据

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.77.100',
        'port': 3306,
        'user': 'pzh',
        'passwd': 'Hello2017',
        'db': 'python',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()


    students = '''set @i := 10000;
            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> select count(*) from Student;

+----------+

| count(*) |

+----------+

|    10000 |

+----------+

1 row in set (0.01 sec)

 

mysql> select count(*) from Course;

+----------+

| count(*) |

+----------+

|       10 |

+----------+

1 row in set (0.00 sec)

 

mysql> select count(*) from Score;

+----------+

| count(*) |

+----------+

|    10000 |

+----------+

1 row in set (0.00 sec)

 

mysql> select count(*) from Teacher;

+----------+

| count(*) |

+----------+

|      100 |

+----------+

1 row in set (0.00 sec)

 

查数据

 

import codecs

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.77.100',
        'port': 3306,
        'user': 'pzh',
        'passwd': 'Hello2017',
        'db': 'python',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()

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

结果：

(19844L, u'315', u'F', 24)

(17156L, u'315', u'F', 25)

(14349L, u'48f', u'F', 25)

(17007L, u'48f', u'F', 25)

(12629L, u'afd', u'F', 25)

(13329L, u'afd', u'F', 24)

(10857L, u'e31', u'F', 23)

(14476L, u'e31', u'M', 21)

(16465L, u'ee5', u'M', 22)

(18570L, u'ee5', u'M', 21)

(17056L, u'ef0', u'M', 23)

(16946L, u'ef0', u'F', 24)

 

 

转载于:https://my.oschina.net/peizh/blog/1577674