python数据库开发_Python-数据库开发

1 classConnection(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 call6 connect().7

8 Establish a connection to the MySQL database. Accepts several9 arguments:10

11 :param host: Host where the database server is located12 :param user: Username to log in as13 :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, specify17 the interface from which to connect to the host. Argument can be18 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 supported43 :param named_pipe: Not supported44 :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 as53 an argument. For the dialog plugin, a prompt(echo, prompt) method can be used54 (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 `_ in the61 specification.62 """

63

64 _sock =None65 _auth_plugin_name = ''

66 _closed =False67 _secure =False68

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 =True83

84 if db is not None and database isNone:85 database =db86 if passwd is not None and notpassword:87 password =passwd88

89 if compress ornamed_pipe:90 raise NotImplementedError("compress and named_pipe arguments are not supported")91

92 self._local_infile =bool(local_infile)93 ifself._local_infile:94 client_flag |=CLIENT.LOCAL_FILES95

96 if read_default_group and notread_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 ifread_default_file:103 if notread_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 ifarg:111 returnarg112 try:113 returncfg.get(read_default_group, key)114 exceptException:115 returnarg116

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 notssl:126 ssl ={}127 ifisinstance(ssl, dict):128 for key in ["ca", "capath", "cert", "key", "cipher"]:129 value = _config("ssl-" +key, ssl.get(key))130 ifvalue:131 ssl[key] =value132

133 self.ssl =False134 ifssl:135 if notSSL_ENABLED:136 raise NotImplementedError("ssl module not found")137 self.ssl =True138 client_flag |=CLIENT.SSL139 self.ctx =self._create_ssl_ctx(ssl)140

141 self.host = host or "localhost"

142 self.port = port or 3306

143 self.user = user orDEFAULT_USER144 self.password = password or b""

145 ifisinstance(self.password, text_type):146 self.password = self.password.encode('latin1')147 self.db =database148 self.unix_socket =unix_socket149 self.bind_address =bind_address150 if not (0 < connect_timeout <= 31536000):151 raise ValueError("connect_timeout should be >0 and <=31536000")152 self.connect_timeout = connect_timeout orNone153 if read_timeout is not None and read_timeout <=0:154 raise ValueError("read_timeout should be >= 0")155 self._read_timeout =read_timeout156 if write_timeout is not None and write_timeout <=0:157 raise ValueError("write_timeout should be >= 0")158 self._write_timeout =write_timeout159 ifcharset:160 self.charset =charset161 self.use_unicode =True162 else:163 self.charset =DEFAULT_CHARSET164 self.use_unicode =False165

166 if use_unicode is notNone:167 self.use_unicode =use_unicode168

169 self.encoding =charset_by_name(self.charset).encoding170

171 client_flag |=CLIENT.CAPABILITIES172 ifself.db:173 client_flag |=CLIENT.CONNECT_WITH_DB174

175 self.client_flag =client_flag176

177 self.cursorclass =cursorclass178

179 self._result =None180 self._affected_rows =0181 self.host_info = "Not connected"

182

183 #: specified autocommit mode. None means use server default.

184 self.autocommit_mode =autocommit185

186 if conv isNone:187 conv =converters.conversions188

189 #Need for MySQLdb compatibility.

190 self.encoders = dict([(k, v) for (k, v) in conv.items() if type(k) is notint])191 self.decoders = dict([(k, v) for (k, v) in conv.items() if type(k) isint])192 self.sql_mode =sql_mode193 self.init_command =init_command194 self.max_allowed_packet =max_allowed_packet195 self._auth_plugin_map = auth_plugin_map or{}196 self._binary_prefix =binary_prefix197 self.server_public_key =server_public_key198

199 self._connect_attrs ={200 '_client_name': 'pymysql',201 '_pid': str(os.getpid()),202 '_client_version': VERSION_STRING,203 }204 ifprogram_name:205 self._connect_attrs["program_name"] =program_name206 elifsys.argv:207 self._connect_attrs["program_name"] =sys.argv[0]208

209 ifdefer_connect:210 self._sock =None211 else:212 self.connect()213

214 def_create_ssl_ctx(self, sslp):215 ifisinstance(sslp, ssl.SSLContext):216 returnsslp217 ca = sslp.get('ca')218 capath = sslp.get('capath')219 hasnoca = ca is None and capath isNone220 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 elsessl.CERT_REQUIRED223 if 'cert' insslp:224 ctx.load_cert_chain(sslp['cert'], keyfile=sslp.get('key'))225 if 'cipher' insslp:226 ctx.set_ciphers(sslp['cipher'])227 ctx.options |=ssl.OP_NO_SSLv2228 ctx.options |=ssl.OP_NO_SSLv3229 returnctx230

231 defclose(self):232 """

233 Send the quit message and close the socket.234

235 See `Connection.close() `_236 in the specification.237

238 :raise Error: If the connection is already closed.239 """

240 ifself._closed:241 raise err.Error("Already closed")242 self._closed =True243 if self._sock isNone:244 return

245 send_data = struct.pack('

250 finally:251 self._force_close()252

253 @property254 defopen(self):255 """Return True if the connection is open"""

256 return self._sock is notNone257

258 def_force_close(self):259 """Close connection without QUIT message"""

260 ifself._sock:261 try:262 self._sock.close()263 except: #noqa

264 pass

265 self._sock =None266 self._rfile =None267

268 __del__ =_force_close269

270 defautocommit(self, value):271 self.autocommit_mode =bool(value)272 current =self.get_autocommit()273 if value !=current:274 self._send_autocommit_mode()275

276 defget_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 notpkt.is_ok_packet():283 raise err.OperationalError(2014, "Command Out of Sync")284 ok =OKPacketWrapper(pkt)285 self.server_status =ok.server_status286 returnok287

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 defbegin(self):295 """Begin transaction."""

296 self._execute_command(COMMAND.COM_QUERY, "BEGIN")297 self._read_ok_packet()298

299 defcommit(self):300 """

301 Commit changes to stable storage.302

303 See `Connection.commit() `_304 in the specification.305 """

306 self._execute_command(COMMAND.COM_QUERY, "COMMIT")307 self._read_ok_packet()308

309 defrollback(self):310 """

311 Roll back the current transaction.312

313 See `Connection.rollback() `_314 in the specification.315 """

316 self._execute_command(COMMAND.COM_QUERY, "ROLLBACK")317 self._read_ok_packet()318

319 defshow_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 returnresult.rows325

326 defselect_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 ifisinstance(obj, str_type):341 return "'" + self.escape_string(obj) + "'"

342 ifisinstance(obj, (bytes, bytearray)):343 ret =self._quote_bytes(obj)344 ifself._binary_prefix:345 ret = "_binary" +ret346 returnret347 return converters.escape_item(obj, self.charset, mapping=mapping)348

349 defliteral(self, obj):350 """Alias for escape()351

352 Non-standard, for internal use; do not use this in your applications.353 """

354 returnself.escape(obj, self.encoders)355

356 defescape_string(self, s):357 if (self.server_status &

358 SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES):359 return s.replace("'", "''")360 returnconverters.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 returnconverters.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 ifcursor:377 returncursor(self)378 returnself.cursorclass(self)379

380 def __enter__(self):381 """Context manager that returns a Cursor"""

382 returnself.cursor()383

384 def __exit__(self, exc, value, traceback):385 """On successful exit, commit. On exception, rollback"""

386 ifexc: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 orIRONPYTHON):396 ifPY2: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 returnself._affected_rows403

404 def next_result(self, unbuffered=False):405 self._affected_rows = self._read_query_result(unbuffered=unbuffered)406 returnself._affected_rows407

408 defaffected_rows(self):409 returnself._affected_rows410

411 defkill(self, thread_id):412 arg = struct.pack('

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 isNone:424 ifreconnect:425 self.connect()426 reconnect =False427 else:428 raise err.Error("Already closed")429 try:430 self._execute_command(COMMAND.COM_PING, "")431 self._read_ok_packet()432 exceptException:433 ifreconnect:434 self.connect()435 self.ping(False)436 else:437 raise

438

439 defset_charset(self, charset):440 #Make sure charset is supported.

441 encoding =charset_by_name(charset).encoding442

443 self._execute_command(COMMAND.COM_QUERY, "SET NAMES %s" %self.escape(charset))444 self._read_packet()445 self.charset =charset446 self.encoding =encoding447

448 def connect(self, sock=None):449 self._closed =False450 try:451 if sock isNone:452 ifself.unix_socket:453 sock =socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)454 sock.settimeout(self.connect_timeout)455 sock.connect(self.unix_socket)456 self.host_info = "Localhost via UNIX socket"

457 self._secure =True458 if DEBUG: print('connected using unix_socket')459 else:460 kwargs ={}461 if self.bind_address is notNone:462 kwargs['source_address'] =(self.bind_address, 0)463 whileTrue:464 try:465 sock =socket.create_connection(466 (self.host, self.port), self.connect_timeout,467 **kwargs)468 break

469 except(OSError, IOError) as e:470 if e.errno ==errno.EINTR:471 continue

472 raise

473 self.host_info = "socket %s:%d" %(self.host, self.port)474 if DEBUG: print('connected using socket')475 sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_NODELAY, 1)476 sock.settimeout(None)477 sock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)478 self._sock =sock479 self._rfile = _makefile(sock, 'rb')480 self._next_seq_id =0481

482 self._get_server_information()483 self._request_authentication()484

485 if self.sql_mode is notNone:486 c =self.cursor()487 c.execute("SET sql_mode=%s", (self.sql_mode,))488

489 if self.init_command is notNone:490 c =self.cursor()491 c.execute(self.init_command)492 c.close()493 self.commit()494

495 if self.autocommit_mode is notNone:496 self.autocommit(self.autocommit_mode)497 exceptBaseException as e:498 self._rfile =None499 if sock is notNone:500 try:501 sock.close()502 except: #noqa

503 pass

504

505 ifisinstance(e, (OSError, IOError, socket.error)):506 exc =err.OperationalError(507 2003,508 "Can't connect to MySQL server on %r (%s)" %(509 self.host, e))510 #Keep original exception and traceback to investigate error.

511 exc.original_exception =e512 exc.traceback =traceback.format_exc()513 if DEBUG: print(exc.traceback)514 raiseexc515

516 #If e is neither DatabaseError or IOError, It's a bug.

517 #But raising AssertionError hides original error.

518 #So just reraise it.

519 raise

520

521 defwrite_packet(self, payload):522 """Writes an entire "mysql packet" in its entirety to the network523 addings its length and sequence number.524 """

525 #Internal note: when you build packet manualy and calls _write_bytes()

526 #directly, you should set self._next_seq_id properly.

527 data = pack_int24(len(payload)) + int2byte(self._next_seq_id) +payload528 ifDEBUG: dump_packet(data)529 self._write_bytes(data)530 self._next_seq_id = (self._next_seq_id + 1) % 256

531

532 def _read_packet(self, packet_type=MysqlPacket):533 """Read an entire "mysql packet" in its entirety from the network534 and return a MysqlPacket type that represents the results.535

536 :raise OperationalError: If the connection to the MySQL server is lost.537 :raise InternalError: If the packet sequence number is wrong.538 """

539 buff = b''

540 whileTrue:541 packet_header = self._read_bytes(4)542 #if DEBUG: dump_packet(packet_header)

543

544 btrl, btrh, packet_number = struct.unpack('

550 raiseerr.OperationalError(551 CR.CR_SERVER_LOST,552 "Lost connection to MySQL server during query")553 raiseerr.InternalError(554 "Packet sequence number wrong - got %d expected %d"

555 %(packet_number, self._next_seq_id))556 self._next_seq_id = (self._next_seq_id + 1) % 256

557

558 recv_data =self._read_bytes(bytes_to_read)559 ifDEBUG: dump_packet(recv_data)560 buff +=recv_data561 #https://dev.mysql.com/doc/internals/en/sending-more-than-16mbyte.html

562 if bytes_to_read == 0xffffff:563 continue

564 if bytes_to_read

&CLIENT.SSL:681

&CLIENT.SSL:706

&CLIENT.PLUGIN_AUTH_LENENC_CLIENT_DATA:713&CLIENT.SECURE_CONNECTION:715

&CLIENT.CONNECT_WITH_DB:720

&CLIENT.PLUGIN_AUTH:725

&CLIENT.CONNECT_ATTRS:728

&

&

&

<<

&

<