python操作mysql数据库的例子-马育民老师

# 准备

### 创建数据库

创建下面数据库：
```
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()
```

原文出处：http://malaoshi.top/show_1EF3VbCimNoN.html