0
# Database Connections
1
2
Create and manage individual MySQL database connections with full async/await support. Connections provide the foundation for all database operations in aiomysql.
3
4
## Capabilities
5
6
### Connection Creation
7
8
Create a new MySQL database connection with comprehensive configuration options.
9
10
```python { .api }
11
def connect(
12
host: str = "localhost",
13
user: str = None,
14
password: str = "",
15
db: str = None,
16
port: int = 3306,
17
unix_socket: str = None,
18
charset: str = '',
19
sql_mode: str = None,
20
read_default_file: str = None,
21
conv: dict = None,
22
use_unicode: bool = None,
23
client_flag: int = 0,
24
cursorclass: type = Cursor,
25
init_command: str = None,
26
connect_timeout: int = None,
27
read_default_group: str = None,
28
autocommit: bool = False,
29
echo: bool = False,
30
local_infile: bool = False,
31
loop = None,
32
ssl: dict = None,
33
auth_plugin: str = '',
34
program_name: str = '',
35
server_public_key: str = None
36
) -> _ConnectionContextManager:
37
"""
38
Create a MySQL database connection.
39
40
Parameters:
41
- host: MySQL server hostname
42
- user: Username for authentication
43
- password: Password for authentication
44
- db: Database name to connect to
45
- port: MySQL server port (default 3306)
46
- unix_socket: Unix socket path for local connections
47
- charset: Character set for the connection
48
- sql_mode: SQL mode for the session
49
- read_default_file: MySQL configuration file to read
50
- conv: Custom type converters dictionary
51
- use_unicode: Enable Unicode support
52
- client_flag: MySQL client flags
53
- cursorclass: Default cursor class for new cursors
54
- init_command: SQL command to run on connection
55
- connect_timeout: Connection timeout in seconds
56
- read_default_group: Configuration file group to read
57
- autocommit: Enable autocommit mode
58
- echo: Enable query logging
59
- local_infile: Enable LOAD DATA LOCAL INFILE
60
- loop: Event loop to use
61
- ssl: SSL configuration dictionary
62
- auth_plugin: Authentication plugin name
63
- program_name: Program name for connection
64
- server_public_key: Server's public key for authentication
65
66
Returns:
67
Connection object
68
"""
69
```
70
71
### Connection Management
72
73
The Connection class provides methods for managing database connections and executing transactions.
74
75
```python { .api }
76
class Connection:
77
@property
78
def closed(self) -> bool: ...
79
80
@property
81
def host(self) -> str: ...
82
83
@property
84
def port(self) -> int: ...
85
86
@property
87
def user(self) -> str: ...
88
89
@property
90
def db(self) -> str: ...
91
92
@property
93
def charset(self) -> str: ...
94
95
@property
96
def encoding(self) -> str: ...
97
98
def cursor(self, cursor=None) -> Cursor:
99
"""
100
Create a new cursor for executing queries.
101
102
Parameters:
103
- cursor: Cursor class to use (default: connection's cursorclass)
104
105
Returns:
106
Cursor instance
107
"""
108
109
async def ping(self, reconnect: bool = True) -> None:
110
"""
111
Check if the server is alive and optionally reconnect.
112
113
Parameters:
114
- reconnect: Whether to reconnect if connection is lost
115
"""
116
117
def close(self) -> None:
118
"""
119
Close the connection immediately without sending quit command.
120
"""
121
122
async def ensure_closed(self) -> None:
123
"""
124
Send quit command and close the connection gracefully.
125
"""
126
127
async def autocommit(self, value: bool) -> None:
128
"""
129
Enable or disable autocommit mode.
130
131
Parameters:
132
- value: True to enable autocommit, False to disable
133
"""
134
135
async def begin(self) -> None:
136
"""
137
Begin a new transaction.
138
"""
139
140
async def commit(self) -> None:
141
"""
142
Commit the current transaction.
143
"""
144
145
async def rollback(self) -> None:
146
"""
147
Rollback the current transaction.
148
"""
149
150
async def select_db(self, db: str) -> None:
151
"""
152
Change the default database for the connection.
153
154
Parameters:
155
- db: Database name to switch to
156
"""
157
158
async def show_warnings(self) -> tuple:
159
"""
160
Retrieve MySQL warnings from the last executed statement.
161
162
Returns:
163
Tuple of warning information
164
"""
165
166
async def set_charset(self, charset: str) -> None:
167
"""
168
Set the character set for the connection.
169
170
Parameters:
171
- charset: Character set name
172
"""
173
174
def escape(self, obj) -> str:
175
"""
176
Escape a value for safe use in SQL queries.
177
178
Parameters:
179
- obj: Value to escape
180
181
Returns:
182
Escaped string representation
183
"""
184
185
def literal(self, obj) -> str:
186
"""
187
Alias for escape() method.
188
189
Parameters:
190
- obj: Value to escape
191
192
Returns:
193
Escaped string representation
194
"""
195
196
def escape_string(self, s: str) -> str:
197
"""
198
Escape a string value for SQL queries.
199
200
Parameters:
201
- s: String to escape
202
203
Returns:
204
Escaped string
205
"""
206
```
207
208
## Usage Examples
209
210
### Basic Connection
211
212
```python
213
import asyncio
214
import aiomysql
215
216
async def connect_example():
217
# Create connection
218
conn = await aiomysql.connect(
219
host='localhost',
220
port=3306,
221
user='myuser',
222
password='mypass',
223
db='mydatabase'
224
)
225
226
# Use connection
227
async with conn.cursor() as cur:
228
await cur.execute("SELECT VERSION()")
229
version = await cur.fetchone()
230
print(f"MySQL version: {version[0]}")
231
232
# Close connection
233
conn.close()
234
235
asyncio.run(connect_example())
236
```
237
238
### Connection with SSL
239
240
```python
241
async def ssl_connection():
242
ssl_config = {
243
'ca': '/path/to/ca.pem',
244
'cert': '/path/to/client-cert.pem',
245
'key': '/path/to/client-key.pem'
246
}
247
248
conn = await aiomysql.connect(
249
host='secure-mysql-server.com',
250
user='myuser',
251
password='mypass',
252
db='mydatabase',
253
ssl=ssl_config
254
)
255
256
# Use secure connection
257
async with conn.cursor() as cur:
258
await cur.execute("SELECT * FROM sensitive_data LIMIT 1")
259
data = await cur.fetchone()
260
print(data)
261
262
conn.close()
263
```
264
265
### Transaction Management
266
267
```python
268
async def transaction_example():
269
conn = await aiomysql.connect(
270
host='localhost',
271
user='myuser',
272
password='mypass',
273
db='mydatabase',
274
autocommit=False # Disable autocommit for explicit transactions
275
)
276
277
try:
278
# Begin transaction
279
await conn.begin()
280
281
async with conn.cursor() as cur:
282
# Execute multiple statements in transaction
283
await cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)",
284
("John Doe", "john@example.com"))
285
await cur.execute("INSERT INTO profiles (user_id, bio) VALUES (LAST_INSERT_ID(), %s)",
286
("Software developer",))
287
288
# Commit transaction
289
await conn.commit()
290
print("Transaction committed successfully")
291
292
except Exception as e:
293
# Rollback on error
294
await conn.rollback()
295
print(f"Transaction rolled back: {e}")
296
297
finally:
298
conn.close()
299
```