Share a MySQL client operation library implemented in pure Python-Mysql Tutorial-php.cn

PyMySQL is a MySQL client operation library implemented in pure Python, supporting transactions, stored procedures, batch execution, etc. PyMySQL follows the Python Database API v2.0 specification and includes the pure-Python MySQL client library.

Installation

pip install PyMySQL

Copy after login

Create database connection

import pymysql

connection = pymysql.connect(host=&#39;localhost&#39;,
                             port=3306,
                             user=&#39;root&#39;,
                             password=&#39;root&#39;,
                             db=&#39;demo&#39;,
                             charset=&#39;utf8&#39;)

Copy after login

Parameter list:

Parameter	Description
host	Database server address, default localhost
user	Username, defaults to the user running the current program
password	Login password, defaults to an empty string
database	Default operation database
port	Database port, the default is 3306
bind_address	When the client has multiple network interfaces, specify the interface to connect to the host. Parameters can be hostnames or IP addresses.
unix_socket	unix socket address, different from host connection
read_timeout	Read Data timeout, in seconds, unlimited by default
write_timeout	Write data timeout, in seconds, unlimited by default
charset	Database encoding
sql_mode	Specify the default SQL_MODE
read_default_file	Specifies my.cnf file to read these parameters from under the [client] section.
conv	Conversion dictionary to use instead of the default one. This is used to provide custom marshalling and unmarshaling of types.
use_unicode	Whether or not to default to unicode strings. This option defaults to true for Py3k.
client_flag	Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass	Set the default cursor type
init_command	Initialization SQL statement executed after the connection is established
connect_timeout	Connection timeout, default 10, minimum 1, maximum 31536000
ssl	A dict of arguments similar to mysql_ssl_set()'s parameters. For now the capath and cipher arguments are not supported.
read_default_group	Group to read from in the configuration file.
compress	Not supported
named_pipe	Not supported
autocommit	Is it automatic Submit, the default is not to submit automatically, the parameter value is None, which means it is subject to the server
local_infile	Boolean to enable the use of LOAD DATA LOCAL command. (default: False )
max_allowed_packet	The maximum amount of data sent to the server, the default is 16MB
defer_connect	Whether to connect lazily, the default is to connect immediately
auth_plugin_map	A dict of plugin names to a class that processes that plugin. The class will take the Connection object as the argument to the constructor. The class needs an authenticate method taking an authentication packet as an argument. For the dialog plugin, a prompt(echo, prompt) method can be used (if no authenticate method) for returning a string from the user. (experimental )
server_public_key	SHA256 authenticaiton plugin public key value. (default: None)
db	Alias of parameter database
passwd	Alias of parameter password
binary_prefix	Add _binary prefix on bytes and bytearray. (default: False)

执行 SQL

cursor.execute(sql, args) 执行单条 SQL

# 获取游标
cursor = connection.cursor()

# 创建数据表
effect_row = cursor.execute(&#39;&#39;&#39;
CREATE TABLE `users` (
  `name` varchar(32) NOT NULL,
  `age` int(10) unsigned NOT NULL DEFAULT &#39;0&#39;,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
&#39;&#39;&#39;)

# 插入数据(元组或列表)
effect_row = cursor.execute(&#39;INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)&#39;, (&#39;mary&#39;, 18))

# 插入数据(字典)
info = {&#39;name&#39;: &#39;fake&#39;, &#39;age&#39;: 15}
effect_row = cursor.execute(&#39;INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)&#39;, info)

connection.commit()

Copy after login

executemany(sql, args) 批量执行 SQL

# 获取游标
cursor = connection.cursor()

# 批量插入
effect_row = cursor.executemany(
    &#39;INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)&#39;, [
        (&#39;hello&#39;, 13),
        (&#39;fake&#39;, 28),
    ])

connection.commit()

Copy after login

注意：INSERT、UPDATE、DELETE 等修改数据的语句需手动执行connection.commit()完成对数据修改的提交。

获取自增 ID

cursor.lastrowid

Copy after login

查询数据

# 执行查询 SQL
cursor.execute(&#39;SELECT * FROM `users`&#39;)

# 获取单条数据
cursor.fetchone()

# 获取前N条数据
cursor.fetchmany(3)

# 获取所有数据
cursor.fetchall()

Copy after login

游标控制

所有的数据查询操作均基于游标，我们可以通过cursor.scroll(num, mode)控制游标的位置。

cursor.scroll(1, mode=&#39;relative&#39;) # 相对当前位置移动
cursor.scroll(2, mode=&#39;absolute&#39;) # 相对绝对位置移动

Copy after login

设置游标类型

查询时，默认返回的数据类型为元组，可以自定义设置返回类型。支持5种游标类型：

Cursor: 默认，元组类型
DictCursor: 字典类型
DictCursorMixin: 支持自定义的游标类型，需先自定义才可使用
SSCursor: 无缓冲元组类型
SSDictCursor: 无缓冲字典类型

无缓冲游标类型，适用于数据量很大，一次性返回太慢，或者服务端带宽较小时。源码注释：

Unbuffered Cursor, mainly useful for queries that return a lot of data, or for connections to remote servers over a slow network.
Instead of copying every row of data into a buffer, this will fetch rows as needed. The upside of this is the client uses much less memory, and rows are returned much faster when traveling over a slow network
or if the result set is very big.
There are limitations, though. The MySQL protocol doesn't support returning the total number of rows, so the only way to tell how many rows there are is to iterate over every row returned. Also, it currently isn't possible to scroll backwards, as only the current row is held in memory.

创建连接时，通过 cursorclass 参数指定类型：

connection = pymysql.connect(host=&#39;localhost&#39;,
                             user=&#39;root&#39;,
                             password=&#39;root&#39;,
                             db=&#39;demo&#39;,
                             charset=&#39;utf8&#39;,
                             cursorclass=pymysql.cursors.DictCursor)

Copy after login

也可以在创建游标时指定类型：

cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

Copy after login

事务处理

开启事务

connection.begin()

提交修改

connection.commit()

回滚事务

connection.rollback()

防 SQL 注入

转义特殊字符
connection.escape_string(str)
参数化语句
支持传入参数进行自动转义、格式化 SQL 语句，以避免 SQL 注入等安全问题。

# 插入数据(元组或列表)
effect_row = cursor.execute(&#39;INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)&#39;, (&#39;mary&#39;, 18))

# 插入数据(字典)
info = {&#39;name&#39;: &#39;fake&#39;, &#39;age&#39;: 15}
effect_row = cursor.execute(&#39;INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)&#39;, info)

# 批量插入
effect_row = cursor.executemany(
    &#39;INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)&#39;, [
        (&#39;hello&#39;, 13),
        (&#39;fake&#39;, 28),
    ])

Copy after login

参考资料

Python中操作mysql的pymysql模块详解
Python之pymysql的使用

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

How to fix KB5055518 fails to install in Windows 10?

1 months ago By DDD

How to fix KB5055523 fails to install in Windows 11?

1 months ago By DDD

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks ago By DDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

How to fix KB5055612 fails to install in Windows 10?

3 weeks ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1664

CakePHP Tutorial

1423

Laravel Tutorial

1317

PHP Tutorial

1268

C# Tutorial

1246

Related knowledge

MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

Explain the purpose of foreign keys in MySQL. Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

Golang vs. Python: The Pros and Cons Apr 21, 2025 am 12:17 AM

Golangisidealforbuildingscalablesystemsduetoitsefficiencyandconcurrency,whilePythonexcelsinquickscriptinganddataanalysisduetoitssimplicityandvastecosystem.Golang'sdesignencouragesclean,readablecodeanditsgoroutinesenableefficientconcurrentoperations,t

Python vs. C : Understanding the Key Differences Apr 21, 2025 am 12:18 AM

Python and C each have their own advantages, and the choice should be based on project requirements. 1) Python is suitable for rapid development and data processing due to its concise syntax and dynamic typing. 2)C is suitable for high performance and system programming due to its static typing and manual memory management.

Laravel vs. Python (with Frameworks): A Comparative Analysis Apr 21, 2025 am 12:15 AM

Laravel is suitable for projects that teams are familiar with PHP and require rich features, while Python frameworks depend on project requirements. 1.Laravel provides elegant syntax and rich features, suitable for projects that require rapid development and flexibility. 2. Django is suitable for complex applications because of its "battery inclusion" concept. 3.Flask is suitable for fast prototypes and small projects, providing great flexibility.

Python vs. JavaScript: Development Environments and Tools Apr 26, 2025 am 12:09 AM

Both Python and JavaScript's choices in development environments are important. 1) Python's development environment includes PyCharm, JupyterNotebook and Anaconda, which are suitable for data science and rapid prototyping. 2) The development environment of JavaScript includes Node.js, VSCode and Webpack, which are suitable for front-end and back-end development. Choosing the right tools according to project needs can improve development efficiency and project success rate.

Python vs. C : Which Language to Choose for Your Project? Apr 21, 2025 am 12:17 AM

Choosing Python or C depends on project requirements: 1) If you need rapid development, data processing and prototype design, choose Python; 2) If you need high performance, low latency and close hardware control, choose C.

Compare and contrast MySQL and MariaDB. Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

See all articles