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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
# asyncmy - A fast asyncio MySQL/MariaDB driver
[](https://pypi.python.org/pypi/asyncmy)
[](https://github.com/long2ice/asyncmy)
[](https://github.com/long2ice/asyncmy/actions/workflows/pypi.yml)
[](https://github.com/long2ice/asyncmy/actions/workflows/ci.yml)
## Introduction
`asyncmy` is a fast asyncio MySQL/MariaDB driver, which reuse most of [pymysql](https://github.com/PyMySQL/PyMySQL)
and [aiomysql](https://github.com/aio-libs/aiomysql) but rewrite core protocol with [cython](https://cython.org/) to
speedup.
## Features
- API compatible with [aiomysql](https://github.com/aio-libs/aiomysql).
- Faster by [cython](https://cython.org/).
- MySQL replication protocol support with `asyncio`.
- Tested both MySQL and MariaDB in [CI](https://github.com/long2ice/asyncmy/blob/dev/.github/workflows/ci.yml).
## Benchmark
The result comes from [benchmark](./benchmark).
> The device is iMac Pro(2017) i9 3.6GHz 48G and MySQL version is 8.0.26.
### Conclusion
- There is no doubt that `mysqlclient` is the fastest MySQL driver.
- All kinds of drivers have a small gap except `select`.
- `asyncio` could enhance `insert`.
- `asyncmy` performs remarkable when compared to other drivers.
## Install
```shell
pip install asyncmy
```
### Installing on Windows
To install asyncmy on Windows, you need to install the tools needed to build it.
1. Download *Microsoft C++ Build Tools* from https://visualstudio.microsoft.com/visual-cpp-build-tools/
2. Run CMD as Admin (not required but recommended) and navigate to the folder when your installer is downloaded
3. Installer executable should look like this `vs_buildtools__XXXXXXXXX.XXXXXXXXXX.exe`, it will be easier if you rename
it to just `vs_buildtools.exe`
4. Run this command (Make sure you have about 5-6GB of free storage)
```shell
vs_buildtools.exe --norestart --passive --downloadThenInstall --includeRecommended --add Microsoft.VisualStudio.Workload.NativeDesktop --add Microsoft.VisualStudio.Workload.VCTools --add Microsoft.VisualStudio.Workload.MSBuildTools
```
5. Wait until the installation is finished
6. After installation will finish, restart your computer
7. Install asyncmy via PIP
```shell
pip install asyncmy
```
Now you can uninstall previously installed tools.
## Usage
### Use `connect`
`asyncmy` provides a way to connect to MySQL database with simple factory function `asyncmy.connect()`. Use this
function if you want just one connection to the database, consider connection pool for multiple connections.
```py
import asyncio
import os
from asyncmy import connect
from asyncmy.cursors import DictCursor
async def run():
conn = await connect(user=os.getenv("DB_USER"), password=os.getenv("DB_PASSWORD", ""))
async with conn.cursor(cursor=DictCursor) as cursor:
await cursor.execute("CREATE DATABASE IF NOT EXISTS test")
await cursor.execute("""
"""
CREATE TABLE IF NOT EXISTS test.`asyncmy` (
`id` int primary key AUTO_INCREMENT,
`decimal` decimal(10, 2),
`date` date,
`datetime` datetime,
`float` float,
`string` varchar(200),
`tinyint` tinyint
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
""".strip()
)
await conn.ensure_closed()
if __name__ == "__main__":
asyncio.run(run())
```
### Use `pool`
`asyncmy` provides connection pool as well as plain Connection objects.
```py
import asyncmy
import asyncio
async def run():
pool = await asyncmy.create_pool()
async with pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT 1")
ret = await cursor.fetchone()
assert ret == (1,)
pool.close()
await pool.wait_closed()
if __name__ == '__main__':
asyncio.run(run())
```
## Replication
`asyncmy` supports MySQL replication protocol
like [python-mysql-replication](https://github.com/noplay/python-mysql-replication), but powered by `asyncio`.
```py
from asyncmy import connect
from asyncmy.replication import BinLogStream
import asyncio
async def run():
conn = await connect()
ctl_conn = await connect()
stream = BinLogStream(
conn,
ctl_conn,
1,
master_log_file="binlog.000172",
master_log_position=2235312,
resume_stream=True,
blocking=True,
)
async for event in stream:
print(event)
await conn.ensure_closed()
await ctl_conn.ensure_closed()
if __name__ == '__main__':
asyncio.run(run())
```
## ThanksTo
> asyncmy is build on top of these awesome projects.
- [pymysql](https://github/pymysql/PyMySQL), a pure python MySQL client.
- [aiomysql](https://github.com/aio-libs/aiomysql), a library for accessing a MySQL database from the asyncio.
- [python-mysql-replication](https://github.com/noplay/python-mysql-replication), pure Python Implementation of MySQL
replication protocol build on top of PyMYSQL.
## License
This project is licensed under the [Apache-2.0](./LICENSE) License.
|
