时间：2021-07-01 10:21:17 帮助过：7人阅读
MySQL基于TCP 协议之上开发，但是网络连接后，传输的数据必须遵循MySQL的协议。
封装好 MySQL协议的包，就是驱动程序
MySQL的驱动：
2、pymysql使用
安装：pip install pymysql
创建数据库和表：
1 CREATE DATABASE IF NOT EXISTS school; 2 SHOW DATABASES ; 3 USE school 4 5 CREATE TABLE `students` ( 6 id int(10) NOT NULL AUTO_INCREMENT, 7 name VARCHAR(20) NOT NULL , 8 age INT(10) DEFAULT NULL , 9 PRIMARY KEY (id) 10 ) ENGINE =InnoDB DEFAULT CHARSET = utf8mb4
连接Connect：
首先，必须建立一个传输数据通道------连接
pymsql.connect() 方法返回的是Connections 模块下的Connection类实例，connect方法传参就是给Connection类的__init__ 提供参数。
1 class Connection(object): 2 """ 3 Representation of a socket with a mysql server. 4 5 The proper way to get an instance of this class is to call 6 connect(). 7 8 Establish a connection to the MySQL database. Accepts several 9 arguments: 10 11 :param host: Host where the database server is located 12 :param user: Username to log in as 13 :param password: Password to use. 14 :param database: Database to use, None to not use a particular one. 15 :param port: MySQL port to use, default is usually OK. (default: 3306) 16 :param bind_address: When the client has multiple network interfaces, specify 17 the interface from which to connect to the host. Argument can be 18 a hostname or an IP address. 19 :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP. 20 :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout) 21 :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout) 22 :param charset: Charset you want to use. 23 :param sql_mode: Default SQL_MODE to use. 24 :param read_default_file: 25 Specifies my.cnf file to read these parameters from under the [client] section. 26 :param conv: 27 Conversion dictionary to use instead of the default one. 28 This is used to provide custom marshalling and unmarshaling of types. 29 See converters. 30 :param use_unicode: 31 Whether or not to default to unicode strings. 32 This option defaults to true for Py3k. 33 :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT. 34 :param cursorclass: Custom cursor class to use. 35 :param init_command: Initial SQL statement to run when connection is established. 36 :param connect_timeout: Timeout before throwing an exception when connecting. 37 (default: 10, min: 1, max: 31536000) 38 :param ssl: 39 A dict of arguments similar to mysql_ssl_set()‘s parameters. 40 For now the capath and cipher arguments are not supported. 41 :param read_default_group: Group to read from in the configuration file. 42 :param compress: Not supported 43 :param named_pipe: Not supported 44 :param autocommit: Autocommit mode. None means use server default. (default: False) 45 :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False) 46 :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB) 47 Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB). 48 :param defer_connect: Don‘t explicitly connect on contruction - wait for connect call. 49 (default: False) 50 :param auth_plugin_map: A dict of plugin names to a class that processes that plugin. 51 The class will take the Connection object as the argument to the constructor. 52 The class needs an authenticate method taking an authentication packet as 53 an argument. For the dialog plugin, a prompt(echo, prompt) method can be used 54 (if no authenticate method) for returning a string from the user. (experimental) 55 :param server_public_key: SHA256 authenticaiton plugin public key value. (default: None) 56 :param db: Alias for database. (for compatibility to MySQLdb) 57 :param passwd: Alias for password. (for compatibility to MySQLdb) 58 :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False) 59 60 See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the 61 specification. 62 """ 63 64 _sock = None 65 _auth_plugin_name = ‘‘ 66 _closed = False 67 _secure = False 68 69 def __init__(self, host=None, user=None, password="", 70 database=None, port=0, unix_socket=None, 71 charset=‘‘, sql_mode=None, 72 read_default_file=None, conv=None, use_unicode=None, 73 client_flag=0, cursorclass=Cursor, init_command=None, 74 connect_timeout=10, ssl=None, read_default_group=None, 75 compress=None, named_pipe=None, 76 autocommit=False, db=None, passwd=None, local_infile=False, 77 max_allowed_packet=16*1024*1024, defer_connect=False, 78 auth_plugin_map=None, read_timeout=None, write_timeout=None, 79 bind_address=None, binary_prefix=False, program_name=None, 80 server_public_key=None): 81 if use_unicode is None and sys.version_info[0] > 2: 82 use_unicode = True 83 84 if db is not None and database is None: 85 database = db 86 if passwd is not None and not password: 87 password = passwd 88 89 if compress or named_pipe: 90 raise NotImplementedError("compress and named_pipe arguments are not supported") 91 92 self._local_infile = bool(local_infile) 93 if self._local_infile: 94 client_flag |= CLIENT.LOCAL_FILES 95 96 if read_default_group and not read_default_file: 97 if sys.platform.startswith("win"): 98 read_default_file = "c:\\my.ini" 99 else: 100 read_default_file = "/etc/my.cnf" 101 102 if read_default_file: 103 if not read_default_group: 104 read_default_group = "client" 105 106 cfg = Parser() 107 cfg.read(os.path.expanduser(read_default_file)) 108 109 def _config(key, arg): 110 if arg: 111 return arg 112 try: 113 return cfg.get(read_default_group, key) 114 except Exception: 115 return arg 116 117 user = _config("user", user) 118 password = _config("password", password) 119 host = _config("host", host) 120 database = _config("database", database) 121 unix_socket = _config("socket", unix_socket) 122 port = int(_config("port", port)) 123 bind_address = _config("bind-address", bind_address) 124 charset = _config("default-character-set", charset) 125 if not ssl: 126 ssl = {} 127 if isinstance(ssl, dict): 128 for key in ["ca", "capath", "cert", "key", "cipher"]: 129 value = _config("ssl-" + key, ssl.get(key)) 130 if value: 131 ssl[key] = value 132 133 self.ssl = False 134 if ssl: 135 if not SSL_ENABLED: 136 raise NotImplementedError("ssl module not found") 137 self.ssl = True 138 client_flag |= CLIENT.SSL 139 self.ctx = self._create_ssl_ctx(ssl) 140 141 self.host = host or "localhost" 142 self.port = port or 3306 143 self.user = user or DEFAULT_USER 144 self.password = password or b"" 145 if isinstance(self.password, text_type): 146 self.password = self.password.encode(‘latin1‘) 147 self.db = database 148 self.unix_socket = unix_socket 149 self.bind_address = bind_address 150 if not (0 < connect_timeout <= 31536000): 151 raise ValueError("connect_timeout should be >0 and <=31536000") 152 self.connect_timeout = connect_timeout or None 153 if read_timeout is not None and read_timeout <= 0: 154 raise ValueError("read_timeout should be >= 0") 155 self._read_timeout = read_timeout 156 if write_timeout is not None and write_timeout <= 0: 157 raise ValueError("write_timeout should be >= 0") 158 self._write_timeout = write_timeout 159 if charset: 160 self.charset = charset 161 self.use_unicode = True 162 else: 163 self.charset = DEFAULT_CHARSET 164 self.use_unicode = False 165 166 if use_unicode is not None: 167 self.use_unicode = use_unicode 168 169 self.encoding = charset_by_name(self.charset).encoding 170 171 client_flag |= CLIENT.CAPABILITIES 172 if self.db: 173 client_flag |= CLIENT.CONNECT_WITH_DB 174 175 self.client_flag = client_flag 176 177 self.cursorclass = cursorclass 178 179 self._result = None 180 self._affected_rows = 0 181 self.host_info = "Not connected" 182 183 #: specified autocommit mode. None means use server default. 184 self.autocommit_mode = autocommit 185 186 if conv is None: 187 conv = converters.conversions 188 189 # Need for MySQLdb compatibility. 190 self.encoders = dict([(k, v) for (k, v) in conv.items() if type(k) is not int]) 191 self.decoders = dict([(k, v) for (k, v) in conv.items() if type(k) is int]) 192 self.sql_mode = sql_mode 193 self.init_command = init_command 194 self.max_allowed_packet = max_allowed_packet 195 self._auth_plugin_map = auth_plugin_map or {} 196 self._binary_prefix = binary_prefix 197 self.server_public_key = server_public_key 198 199 self._connect_attrs = { 200 ‘_client_name‘: ‘pymysql‘, 201 ‘_pid‘: str(os.getpid()), 202 ‘_client_version‘: VERSION_STRING, 203 } 204 if program_name: 205 self._connect_attrs["program_name"] = program_name 206 elif sys.argv: 207 self._connect_attrs["program_name"] = sys.argv[0] 208 209 if defer_connect: 210 self._sock = None 211 else: 212 self.connect() 213 214 def _create_ssl_ctx(self, sslp): 215 if isinstance(sslp, ssl.SSLContext): 216 return sslp 217 ca = sslp.get(‘ca‘) 218 capath = sslp.get(‘capath‘) 219 hasnoca = ca is None and capath is None 220 ctx = ssl.create_default_context(cafile=ca, capath=capath) 221 ctx.check_hostname = not hasnoca and sslp.get(‘check_hostname‘, True) 222 ctx.verify_mode = ssl.CERT_NONE if hasnoca else ssl.CERT_REQUIRED 223 if ‘cert‘ in sslp: 224 ctx.load_cert_chain(sslp[‘cert‘], keyfile=sslp.get(‘key‘)) 225 if ‘cipher‘ in sslp: 226 ctx.set_ciphers(sslp[‘cipher‘]) 227 ctx.options |= ssl.OP_NO_SSLv2 228 ctx.options |= ssl.OP_NO_SSLv3 229 return ctx 230 231 def close(self): 232 """ 233 Send the quit message and close the socket. 234 235 See `Connection.close() <https://www.python.org/dev/peps/pep-0249/#Connection.close>`_ 236 in the specification. 237 238 :raise Error: If the connection is already closed. 239 """ 240 if self._closed: 241 raise err.Error("Already closed") 242 self._closed = True 243 if self._sock is None: 244 return 245 send_data = struct.pack(‘<iB‘, 1, COMMAND.COM_QUIT) 246 try: 247 self._write_bytes(send_data) 248 except Exception: 249 pass 250 finally: 251 self._force_close() 252 253 @property 254 def open(self): 255 """Return True if the connection is open""" 256 return self._sock is not None 257 258 def _force_close(self): 259 """Close connection without QUIT message""" 260 if self._sock: 261 try: 262 self._sock.close() 263 except: # noqa 264 pass 265 self._sock = None 266 self._rfile = None 267 268 __del__ = _force_close 269 270 def autocommit(self, value): 271 self.autocommit_mode = bool(value) 272 current = self.get_autocommit() 273 if value != current: 274 self._send_autocommit_mode() 275 276 def get_autocommit(self): 277 return bool(self.server_status & 278 SERVER_STATUS.SERVER_STATUS_AUTOCOMMIT) 279 280 def _read_ok_packet(self): 281 pkt = self._read_packet() 282 if not pkt.is_ok_packet(): 283 raise err.OperationalError(2014, "Command Out of Sync") 284 ok = OKPacketWrapper(pkt) 285 self.server_status = ok.server_status 286 return ok 287 288 def _send_autocommit_mode(self): 289 """Set whether or not to commit after every execute()""" 290 self._execute_command(COMMAND.COM_QUERY, "SET AUTOCOMMIT = %s" % 291 self.escape(self.autocommit_mode)) 292 self._read_ok_packet() 293 294 def begin(self): 295 """Begin transaction.""" 296 self._execute_command(COMMAND.COM_QUERY, "BEGIN") 297 self._read_ok_packet() 298 299 def commit(self): 300 """ 301 Commit changes to stable storage. 302 303 See `Connection.commit() <https://www.python.org/dev/peps/pep-0249/#commit>`_ 304 in the specification. 305 """ 306 self._execute_command(COMMAND.COM_QUERY, "COMMIT") 307 self._read_ok_packet() 308 309 def rollback(self): 310 """ 311 Roll back the current transaction. 312 313 See `Connection.rollback() <https://www.python.org/dev/peps/pep-0249/#rollback>`_ 314 in the specification. 315 """ 316 self._execute_command(COMMAND.COM_QUERY, "ROLLBACK") 317 self._read_ok_packet() 318 319 def show_warnings(self): 320 """Send the "SHOW WARNINGS" SQL command.""" 321 self._execute_command(COMMAND.COM_QUERY, "SHOW WARNINGS") 322 result = MySQLResult(self) 323 result.read() 324 return result.rows 325 326 def select_db(self, db): 327 """ 328 Set current db. 329 330 :param db: The name of the db. 331 """ 332 self._execute_command(COMMAND.COM_INIT_DB, db) 333 self._read_ok_packet() 334 335 def escape(self, obj, mapping=None): 336 """Escape whatever value you pass to it. 337 338 Non-standard, for internal use; do not use this in your applications. 339 """ 340 if isinstance(obj, str_type): 341 return "‘" + self.escape_string(obj) + "‘" 342 if isinstance(obj, (bytes, bytearray)): 343 ret = self._quote_bytes(obj) 344 if self._binary_prefix: 345 ret = "_binary" + ret 346 return ret 347 return converters.escape_item(obj, self.charset, mapping=mapping) 348 349 def literal(self, obj): 350 """Alias for escape() 351 352 Non-standard, for internal use; do not use this in your applications. 353 """ 354 return self.escape(obj, self.encoders) 355 356 def escape_string(self, s): 357 if (self.server_status & 358 SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES): 359 return s.replace("‘", "‘‘") 360 return converters.escape_string(s) 361 362 def _quote_bytes(self, s): 363 if (self.server_status & 364 SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES): 365 return "‘%s‘" % (_fast_surrogateescape(s.replace(b"‘", b"‘‘")),) 366 return converters.escape_bytes(s) 367 368 def cursor(self, cursor=None): 369 """ 370 Create a new cursor to execute queries with. 371 372 :param cursor: The type of cursor to create; one of :py:class:`Cursor`, 373 :py:class:`SSCursor`, :py:class:`DictCursor`, or :py:class:`SSDictCursor`. 374 None means use Cursor. 375 """ 376 if cursor: 377 return cursor(self) 378 return self.cursorclass(self) 379 380 def __enter__(self): 381 """Context manager that returns a Cursor""" 382 return self.cursor() 383 384 def __exit__(self, exc, value, traceback): 385 """On successful exit, commit. On exception, rollback""" 386 if exc: 387 self.rollback() 388 else: 389 self.commit() 390 391 # The following methods are INTERNAL USE ONLY (called from Cursor) 392 def query(self, sql, unbuffered=False): 393 # if DEBUG: 394 # print("DEBUG: sending query:", sql) 395 if isinstance(sql, text_type) and not (JYTHON or IRONPYTHON): 396 if PY2: 397 sql = sql.encode(self.encoding) 398 else: 399 sql = sql.encode(self.encoding, ‘surrogateescape‘) 400 self._execute_command(COMMAND.COM_QUERY, sql) 401 self._affected_rows = self._read_query_result(unbuffered=unbuffered) 402 return self._affected_rows 403 404 def next_result(self, unbuffered=False): 405 self._affected_rows = self._read_query_result(unbuffered=unbuffered) 406 return self._affected_rows 407 408 def affected_rows(self): 409 return self._affected_rows 410 411 def kill(self, thread_id): 412 arg = struct.pack(‘<I‘, thread_id) 413 self._execute_command(COMMAND.COM_PROCESS_KILL, arg) 414 return self._read_ok_packet() 415 416 def ping(self, reconnect=True): 417 """ 418 Check if the server is alive. 419 420 :param reconnect: If the connection is closed, reconnect. 421 :raise Error: If the connection is closed and reconnect=False. 422 """ 423 if self._sock is None: 424 if reconnect: 425 self.connect() 426 reconnect = False 427 else: 428 raise err.Error("Already closed") 429 try: 430 self._execute_command(COMMAND.COM_PING, "") 431 self._read_ok_packet() 432 except Exception: 433 if reconnect: 434 self.connect() 435 self.ping(False) 436 else: 437 raise 438 439 def set_charset(self, charset): 440 # Make sure charset is supported. 441 encoding = charset_by_name(charset).encoding 442 443 self._execute_command(COMMAND.COM_QUERY, "SET NAMES %s" % self.escape(charset)) 444 self._read_packet() 445 self.charset = charset 446 self.encoding = encoding 447 448 def connect(self, sock=None): 449 self._closed = Fal
