0
# CyMySQL
1

2
A high-performance Python MySQL client library forked from PyMySQL, accelerated using Cython for improved performance. CyMySQL implements the Python Database API Specification v2.0 and supports both synchronous and asynchronous operations through asyncio, with features like connection pooling, prepared statements, and comprehensive MySQL protocol implementation.
3

4
## Package Information
5

6
- **Package Name**: CyMySQL
7
- **Language**: Python
8
- **Installation**: `pip install cymysql`
9
- **Cython-free Installation**: `NO_CYTHON=1 pip install cymysql`
10
- **Requirements**: Python 3.10+, MySQL 5.7+/MariaDB
11
- **Repository**: https://github.com/nakagami/CyMySQL
12

13
## Core Imports
14

15
```python
16
import cymysql
17
```
18

19
For dictionary cursors:
20

21
```python
22
from cymysql.cursors import DictCursor
23
```
24

25
For async operations:
26

27
```python
28
import cymysql.aio
29
from cymysql.aio import AsyncDictCursor
30
```
31

32
## Basic Usage
33

34
### Synchronous Database Operations
35

36
```python
37
import cymysql
38

39
# Connect to database
40
conn = cymysql.connect(
41
    host='127.0.0.1',
42
    user='root',
43
    passwd='',
44
    db='database_name'
45
)
46

47
# Execute queries using cursor
48
cur = conn.cursor()
49
cur.execute('SELECT foo, bar FROM baz')
50

51
# Fetch results
52
for row in cur.fetchall():
53
    print(row[0], row[1])
54

55
# Clean up
56
cur.close()
57
conn.close()
58
```
59

60
### Asynchronous Database Operations
61

62
```python
63
import asyncio
64
import cymysql.aio
65

66
async def database_operations():
67
    # Single connection
68
    conn = await cymysql.aio.connect(
69
        host="127.0.0.1",
70
        user="root",
71
        passwd="",
72
        db="database_name"
73
    )
74
    
75
    cur = conn.cursor()
76
    await cur.execute("SELECT 42")
77
    result = await cur.fetchall()
78
    print(result)
79
    
80
    # Connection pool
81
    pool = await cymysql.aio.create_pool(
82
        host="127.0.0.1",
83
        user="root",
84
        passwd="",
85
        db="database_name",
86
        minsize=1,
87
        maxsize=10
88
    )
89
    
90
    async with pool.acquire() as conn:
91
        async with conn.cursor() as cur:
92
            await cur.execute("SELECT * FROM users")
93
            users = await cur.fetchall()
94
    
95
    pool.close()
96
    await pool.wait_closed()
97

98
asyncio.run(database_operations())
99
```
100

101
## Architecture
102

103
CyMySQL follows the Python DB-API 2.0 specification with performance enhancements:
104

105
- **Connection Layer**: Manages MySQL protocol communication with server
106
- **Cursor Layer**: Provides interface for SQL execution and result handling  
107
- **Async Layer**: Asyncio-compatible connection and cursor implementations
108
- **Connection Pooling**: Efficient connection reuse for high-throughput applications
109
- **Cython Acceleration**: Optional Cython compilation for performance-critical components
110
- **Type Conversion**: Automatic conversion between Python and MySQL data types
111

112
## Capabilities
113

114
### Database Connections
115

116
Core connection functionality for establishing and managing database connections with support for SSL, authentication, character sets, and connection parameters.
117

118
```python { .api }
119
def connect(host='localhost', user=None, password='', passwd='', port=3306, db=None, charset='utf8mb4', **kwargs):
120
    """Create database connection."""
121
```
122

123
[Database Connections](./connections.md)
124

125
### Cursor Operations  
126

127
SQL execution interface providing methods for running queries, managing transactions, and retrieving results with support for prepared statements and parameterized queries.
128

129
```python { .api }
130
class Cursor:
131
    def execute(self, query, args=None): ...
132
    def executemany(self, query, args): ...
133
    def fetchone(self): ...
134
    def fetchall(self): ...
135
    def fetchmany(self, size=None): ...
136

137
class DictCursor(Cursor):
138
    """Cursor that returns rows as dictionaries instead of tuples."""
139
    def fetchone(self): ...  # Returns dict
140
    def fetchall(self): ...  # Returns tuple of dicts
141
```
142

143
[Cursor Operations](./cursors.md)
144

145
### Asynchronous Operations
146

147
Asyncio-compatible database operations including async connections, cursors, and connection pooling for high-performance concurrent database access.
148

149
```python { .api }
150
async def connect(host='localhost', user=None, password='', **kwargs):
151
    """Create async database connection."""
152

153
async def create_pool(minsize=1, maxsize=10, **kwargs):
154
    """Create async connection pool."""
155

156
class AsyncDictCursor:
157
    """Async cursor that returns rows as dictionaries."""
158
    async def fetchone(self): ...  # Returns dict
159
    async def fetchall(self): ...  # Returns tuple of dicts
160
```
161

162
[Asynchronous Operations](./async-operations.md)
163

164
### Data Type Handling
165

166
Comprehensive MySQL data type support with automatic conversion between Python and MySQL types, including support for dates, times, binary data, and JSON.
167

168
```python { .api }
169
def escape_string(value): ...
170
def escape_dict(val, charset): ...
171
def Binary(x): ...
172
Date = date
173
Time = time
174
Timestamp = datetime
175
```
176

177
[Data Type Handling](./data-types.md)
178

179
### Error Handling
180

181
Complete exception hierarchy following DB-API 2.0 specification for handling database errors, operational issues, and programming mistakes.
182

183
```python { .api }
184
class MySQLError(Exception): ...
185
class DatabaseError(MySQLError): ...
186
class OperationalError(DatabaseError): ...
187
class ProgrammingError(DatabaseError): ...
188
```
189

190
[Error Handling](./error-handling.md)
191

192
## Database API Specification
193

194
CyMySQL fully implements Python Database API Specification v2.0:
195

196
- **API Level**: 2.0
197
- **Thread Safety**: 1 (Threads may share the module)
198
- **Parameter Style**: format (using %s placeholders)
199

200
## Performance Features
201

202
- **Cython Acceleration**: Core components can be compiled with Cython for improved performance
203
- **Connection Pooling**: Async connection pooling for high-throughput applications  
204
- **Efficient Protocol**: Optimized MySQL protocol implementation
205
- **Memory Management**: Careful memory usage for large result sets