曹耘豪的博客

在 Python 中使用 MySQL

  1. 安装
  2. 使用
    1. conn
    2. cursor
      1. 执行 sql 语句
        1. execute(sql, arg)
        2. executemany(sql, args)
      2. MySQLdb.cursors.Cursor
        1. fetchone() -> tuple
        2. fetchall() -> tuple of tuple
      3. MySQLdb.cursors.DictCursor
        1. fetchone() -> dict
        2. fetchall() -> tuple of dict
  3. 问题

安装

pip install xxorpython -m pip install xx -i https://pypi.douban.com/simple

使用

conn

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'passwd': '123456',
    'db': 'test_db',   # 可选
    'charset': 'utf8', # 可选
    "local_infile": 1, # 可选
}

# 2x，MySQLdb
import MySQLdb

conn = MySQLdb.connect(**config)
cursor = conn.cursor()

# PyMySql
import pymysql

conn = pymysql.connect(**config)
cursor = conn.cursor(pymysql.cursors.DictCursor)

# mysql-connector
from mysql.connector.cursor import MySQLCursorDict
import mysql.connector

conn = mysql.connector.connect(**config)
cursor = MySQLCursorDict(conn)

cursor

执行 sql 语句

execute(sql, arg)
1
2
3
sql = """SELECT * FROM %s WHERE username=%s AND password=%s"""

cursor.execute(sql, (arg1, arg2, arg3)))
executemany(sql, args)
1
2
3
4
5
6
7
8
sql = """SELECT * FROM %s WHERE username=%s AND password=%s"""

args = [
    (arg11, arg12, arg13),
    (arg21, arg22, arg23),
]

cursor.executemany(sql, args))

MySQLdb.cursors.Cursor

1
cursor = conn.cursor()
fetchone() -> tuple
1
2
3
4
5
6
7
8
cursor.execute(sql, (arg1, arg2, arg3))
cursor.fetchone()

# eg
# res == 1
res = cursor.execute('select count(1) from %s where %s=%s', (arg1, arg2, arg3))
# data == ($count, )
data = cursor.fetchone()
fetchall() -> tuple of tuple
1
pass

MySQLdb.cursors.DictCursor

1
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
fetchone() -> dict
1
pass
fetchall() -> tuple of dict
1
pass

问题

  1. 使用pymysql报错：AttributeError: module ‘pymysql._auth‘ has no attribute ‘scramble_old_password‘

    解决：使用0.6.7版本，pip install pymysql==0.6.7

    https://blog.csdn.net/qq_38866586/article/details/121545480

  技术 / Python