python对mysql的操作

左手的ㄟ右手 2023-08-17 16:25 131阅读 0赞

### 一.引入模块 ###

import pymysql

### 二.建表 ###

def create_table():
        db = pymysql.connect(host='localhost', port=3306, user='root', passwd="123zxcv", db='why')
        cursor = db.cursor()
    
        create_sql = \
            '''
            CREATE TABLE `sys_user` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `name` varchar(255) NOT NULL,
            `login_name` varchar(255) NOT NULL,
            `password` varchar(255) NOT NULL,
            `date` datetime DEFAULT NULL,
            `age` int(11) NOT NULL,
            `address` varchar(255) DEFAULT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `name` (`name`)
            ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
            '''
    
        cursor.execute(create_sql)
        db.commit()
        db.close()

### 三.查询 ###

def query_data():
        db = pymysql.connect(host='localhost', port=3306, user='root', passwd="123zxcv", db='why')
        cursor = db.cursor()
        query_sql = 'select * from sys_user where address = "%s"' % "北京"
        cursor.execute(query_sql)
        results = cursor.fetchall()
        for i in results:
            print(i)
    
        db.close()

**输出:**

(1, 'admin', 'admin', '123456', datetime.datetime(2019, 9, 11, 15, 54, 29), 15, '北京')
    (3, 'xyz', 'xyz', '888888', datetime.datetime(2019, 9, 18, 15, 56, 5), 17, '北京')

### 四.插入 ###

def insert_data():
        db = pymysql.connect(host='localhost', port=3306, user='root', passwd="123zxcv", db='why')
        cursor = db.cursor()
        insert_sql = 'insert into sys_user(name,login_name,password,date,age,address) ' \
                     'values ("%s","%s","%s","%s",%d,"%s")' % ("xh", "why", "654321", "2019-09-27 12:59:59", 18, "北京")
        print(insert_sql)
        cursor.execute(insert_sql)
        db.commit()
        db.close()

### 五.更改 ###

def update_data():
        db = pymysql.connect(host='localhost', port=3306, user='root', passwd="123zxcv", db='why')
        cursor = db.cursor()
        update_sql = 'update sys_user set password="%s" where name="%s"' % ("abcdef", "xh")
        cursor.execute(update_sql)
        db.commit()
        db.close()

### 六.删除 ###

def delete_data():
        db = pymysql.connect(host='localhost', port=3306, user='root', passwd="123zxcv", db='why')
        cursor = db.cursor()
        delete_sql = 'delete from sys_user where id =%d' % 4
        cursor.execute(delete_sql)
        db.commit()
        db.close()