0
# Database Connections
1

2
Core connection functionality for establishing and managing database connections with support for SSL, authentication, character sets, and various MySQL connection parameters.
3

4
## Capabilities
5

6
### Connection Creation
7

8
Creates a new database connection with specified parameters. The connection object manages the underlying socket connection to the MySQL server and handles authentication, character set negotiation, and protocol initialization.
9

10
```python { .api }
11
def connect(host="localhost", user=None, passwd="", db=None, port=3306, 
12
           unix_socket=None, charset='', sql_mode=None, read_default_file=None,
13
           client_flag=0, cursorclass=None, init_command=None, connect_timeout=None,
14
           ssl=None, read_default_group=None, compress="", zstd_compression_level=3,
15
           named_pipe=None, conv=None, encoders=None):
16
    """
17
    Create a database connection.
18
    
19
    Parameters:
20
    - host (str): MySQL server hostname or IP address (default: "localhost")
21
    - user (str): Username for authentication
22
    - passwd (str): Password for authentication (default: "")
23
    - port (int): MySQL server port number (default: 3306)
24
    - db (str): Default database name
25
    - unix_socket (str): Unix socket path for local connections
26
    - charset (str): Character set for connection (default: '')
27
    - sql_mode (str): SQL mode setting for connection
28
    - read_default_file (str): MySQL configuration file path
29
    - client_flag (int): Custom flags to send to MySQL
30
    - cursorclass: Default cursor class for this connection
31
    - init_command (str): SQL command to run on connection
32
    - connect_timeout (int): Connection timeout in seconds
33
    - ssl (dict): SSL configuration parameters
34
    - read_default_group (str): Group to read from configuration file
35
    - compress (str): Compression algorithm ("zlib" or "zstd")
36
    - zstd_compression_level (int): ZSTD compression level (1-22, default: 3)
37
    - named_pipe: Not supported (raises NotImplementedError)
38
    - conv: Decoders dictionary for custom type marshalling
39
    - encoders: Encoders dictionary for custom type marshalling
40
    
41
    Returns:
42
    Connection: Active database connection object
43
    
44
    Raises:
45
    OperationalError: Connection failed
46
    InterfaceError: Invalid connection parameters
47
    """
48
```
49

50
### Connection Management
51

52
The Connection class provides methods for managing the database connection lifecycle, executing SQL statements, and controlling transaction behavior.
53

54
```python { .api }
55
class Connection:
56
    def cursor(self, cursor=None):
57
        """
58
        Create a new cursor object for executing SQL statements.
59
        
60
        Parameters:
61
        - cursor: Cursor class to instantiate (optional)
62
        
63
        Returns:
64
        Cursor: New cursor object
65
        """
66
    
67
    def commit(self):
68
        """
69
        Commit current transaction.
70
        
71
        Raises:
72
        OperationalError: Transaction commit failed
73
        """
74
    
75
    def rollback(self):
76
        """
77
        Roll back current transaction.
78
        
79
        Raises:
80
        OperationalError: Transaction rollback failed
81
        """
82
    
83
    def close(self):
84
        """
85
        Close the database connection.
86
        """
87
    
88
    def autocommit(self, value):
89
        """
90
        Enable or disable autocommit mode.
91
        
92
        Parameters:
93
        - value (bool): True to enable autocommit, False to disable
94
        """
95
    
96
    def get_host_info(self):
97
        """
98
        Get information about the connection host.
99
        
100
        Returns:
101
        str: Host connection information
102
        """
103
    
104
    def get_proto_info(self):
105
        """
106
        Get MySQL protocol version.
107
        
108
        Returns:
109
        int: Protocol version number
110
        """
111
    
112
    def get_server_info(self):
113
        """
114
        Get MySQL server version information.
115
        
116
        Returns:
117
        str: Server version string
118
        """
119
    
120
    def open(self):
121
        """
122
        Check if connection is open.
123
        
124
        Returns:
125
        bool: True if connection is open, False otherwise
126
        """
127
    
128
    def ping(self, reconnect=True):
129
        """
130
        Check if connection to server is alive.
131
        
132
        Parameters:
133
        - reconnect (bool): Attempt to reconnect if connection is lost
134
        
135
        Returns:
136
        None
137
        
138
        Raises:
139
        OperationalError: Connection check failed
140
        """
141
    
142
    def set_charset(self, charset):
143
        """
144
        Set connection character set.
145
        
146
        Parameters:
147
        - charset (str): Character set name
148
        """
149
    
150
    def show_warnings(self):
151
        """
152
        Get warnings from last executed statement.
153
        
154
        Returns:
155
        tuple: Warning information
156
        """
157
```
158

159
### Context Manager Support
160

161
Connections support Python context manager protocol for automatic resource cleanup:
162

163
```python
164
# Automatic connection cleanup
165
with cymysql.connect(host='localhost', user='root', db='test') as conn:
166
    cur = conn.cursor()
167
    cur.execute("SELECT 1")
168
    result = cur.fetchone()
169
# Connection automatically closed when leaving context
170
```
171

172
### SSL Configuration
173

174
CyMySQL supports SSL connections with various authentication modes:
175

176
```python
177
# SSL connection with certificate verification
178
conn = cymysql.connect(
179
    host='mysql-server.example.com',
180
    user='secure_user',
181
    password='secure_password',
182
    ssl={
183
        'ca': '/path/to/ca.pem',
184
        'cert': '/path/to/client-cert.pem',
185
        'key': '/path/to/client-key.pem'
186
    },
187
    ssl_verify_cert=True,
188
    ssl_verify_identity=True
189
)
190
```
191

192
### Connection Configuration Files
193

194
Support for MySQL configuration files for connection parameters:
195

196
```python
197
# Load connection settings from my.cnf
198
conn = cymysql.connect(
199
    read_default_file='/etc/mysql/my.cnf',
200
    read_default_group='client'
201
)
202
```
203

204
## Common Connection Patterns
205

206
### Basic Database Connection
207

208
```python
209
import cymysql
210

211
conn = cymysql.connect(
212
    host='localhost',
213
    user='myuser',
214
    password='mypassword', 
215
    database='mydatabase',
216
    charset='utf8mb4'
217
)
218

219
try:
220
    # Use connection
221
    cursor = conn.cursor()
222
    cursor.execute("SELECT VERSION()")
223
    version = cursor.fetchone()
224
    print(f"MySQL version: {version[0]}")
225
finally:
226
    conn.close()
227
```
228

229
### Connection with Error Handling
230

231
```python
232
import cymysql
233
from cymysql import OperationalError, ProgrammingError
234

235
try:
236
    conn = cymysql.connect(
237
        host='localhost',
238
        user='myuser',
239
        password='mypassword',
240
        database='mydatabase',
241
        connect_timeout=5
242
    )
243
    
244
    # Test connection
245
    conn.ping()
246
    print("Connection successful")
247
    
248
except OperationalError as e:
249
    print(f"Connection failed: {e}")
250
except ProgrammingError as e:
251
    print(f"Authentication error: {e}")
252
```