准备

创建数据库

创建下面数据库：

std_python

创建表

CREATE TABLE `user` (
    `id` INT(10) NOT NULL,
    `username` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `password` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

查询某表所有记录

# coding=utf-8

import mysql.connector

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute("select * from user ")
    result=cursor.fetchall()
    print('查询结果有%s行'%cursor.rowcount)
    for item in result:
        print(item)

except mysql.connector.Error as e:
    print('connect fails!{}'.format(e))

finally:
    cursor.close()
    conn.close()

登录（条件查询多条记录）

简单实现

将查询条件参数写在sql语句中

# coding=utf-8

import mysql.connector

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute("select * from user where username='mayumin' and password='123456'")
    result=cursor.fetchall()
    print('查询结果有%s行'%cursor.rowcount)
    for item in result:
        print(item)

except mysql.connector.Error as e:
    print('connect fails!{}'.format(e))

finally:
    cursor.close()
    conn.close()

这种方式会引起一些问题，不要使用

SQL注入bug

# coding=utf-8

import mysql.connector

username = "lilei' #"
password = ""
sql = "SELECT id,username,PASSWORD FROM USER WHERE username='{}' AND PASSWORD='{}'".format(username,password)

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute(sql)
    result=cursor.fetchall()
    print('查询结果有%s行'%cursor.rowcount)
    for item in result:
        print(item)

except mysql.connector.Error as e:
    print('connect fails!{}'.format(e))

finally:
    cursor.close()
    conn.close()

占位符实现方式一

sql用占位符 %s，执行sql时，传递 list、tuple 参数

# coding=utf-8

import mysql.connector

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute("select * from user where username=%s and password=%s ",['mayumin','123456'])
    result=cursor.fetchall()
    print('查询结果有%s行'%cursor.rowcount)
    for item in result:
        print(item)

except mysql.connector.Error as e:
    print('connect fails!{}'.format(e))

finally:
    cursor.close()
    conn.close()

占位符实现方式二

sql用占位符，传递 dict 参数

# coding=utf-8

import mysql.connector

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute("select * from user where username=%(username)s and password=%(password)s ",{'username':'mayumin','password':'123456'})
    result=cursor.fetchall()
    print('查询结果有%s行'%cursor.rowcount)
    for item in result:
        print(item)

except mysql.connector.Error as e:
    print('connect fails!{}'.format(e))

finally:
    cursor.close()
    conn.close()

条件查询一条记录

# coding=utf-8

import mysql.connector

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute("select * from user where id=%s ",['3920e47cf7224e0eb9a1d75f9ded3f4e'])
    result=cursor.fetchone()
    print('查询结果有%s行'%cursor.rowcount)
    print(result)

except mysql.connector.Error as e:
    print('connect fails!{}'.format(e))

finally:
    cursor.close()
    conn.close()

因为是根据主键id查询，所以最多只能查询到 一条记录，所以使用

data=cursor.fetchone()

获取一条记录

插入记录

# coding=utf-8

import mysql.connector

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute("insert into user(id,username,password,level) values(%s,%s,%s,%s) ",['1','蔡徐坤','123456','1'])
    conn.commit()
    print('插入%s行'%cursor.rowcount)

finally:
    cursor.close()
    conn.close()

更新记录

# coding=utf-8

import mysql.connector

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute("update user set password=%s where id=%s ",['abcd','1'])
    conn.commit()
    print('修改%s行'%cursor.rowcount)

finally:
    cursor.close()
    conn.close()

删除记录

# coding=utf-8

import mysql.connector

try:
    conn=mysql.connector.connect(user='root',
                                password='',
                                host='127.0.0.1',
                                port='3306',
                                database='std_python',
                                buffered=True    #缓冲游标
                                )
    cursor=conn.cursor()
    cursor.execute("delete from user where id=%s ",['1'])
    conn.commit()
    print('删除%s行'%cursor.rowcount)

finally:
    cursor.close()
    conn.close()

原文出处：/show_1EF3VbCimNoN.html