def handle_COPY_OUT_RESPONSE(self, data, context): """https://www.postgresql.org/docs/current/protocol-message-formats.html""" is_binary, num_cols = bh_unpack(data) # column_formats = unpack_from('!' + 'h' * num_cols, data, 3) if context.stream is None: raise InterfaceError( "An output stream is required for the COPY OUT response.") elif isinstance(context.stream, TextIOBase): if is_binary: raise InterfaceError( "The COPY OUT stream is binary, but the stream parameter is text." ) else: decode = codecs.getdecoder(self._client_encoding) def w(data): context.stream.write(decode(data)[0]) context.stream_write = w else: context.stream_write = context.stream.write
def array_inspect(array): first_element = None flattened = list(array_flatten(array)) for v in flattened: if v is not None: first_element = v break if first_element is None: oid = VARCHAR elif isinstance(first_element, bool): oid = BOOLEAN elif isinstance(first_element, int): int2_ok, int4_ok, int8_ok = True, True, True for v in flattened: if v is None: continue v_oid = int_oid(v) if v_oid == SMALLINT: continue int2_ok = False if v_oid == INTEGER: continue int4_ok = False if v_oid == BIGINT: continue int8_ok = False if int2_ok: oid = SMALLINT # INT2[] elif int4_ok: oid = INTEGER # INT4[] elif int8_ok: oid = BIGINT # INT8[] else: oid = NUMERIC # NUMERIC[] else: oid, _ = make_param(PY_TYPES, first_element) try: array_oid = PG_ARRAY_TYPES[oid] except KeyError: raise InterfaceError(f"oid {oid} not supported as array contents") try: return PY_TYPES[array_oid] except KeyError: raise InterfaceError(f"array oid {array_oid} not supported")
def array_inspect(self, value): # Check if array has any values. If empty, we can just assume it's an # array of strings first_element = converters.array_find_first_element(value) if first_element is None: oid = 25 # Use binary ARRAY format to avoid having to properly # escape text in the array literals array_oid = converters.PG_ARRAY_TYPES[oid] else: # supported array output typ = type(first_element) if typ == bool: oid = 16 array_oid = converters.PG_ARRAY_TYPES[oid] elif issubclass(typ, int): # special int array support -- send as smallest possible array # type typ = int int2_ok, int4_ok, int8_ok = True, True, True for v in converters.array_flatten(value): if v is None: continue if min_int2 < v < max_int2: continue int2_ok = False if min_int4 < v < max_int4: continue int4_ok = False if min_int8 < v < max_int8: continue int8_ok = False if int2_ok: array_oid = 1005 # INT2[] elif int4_ok: array_oid = 1007 # INT4[] elif int8_ok: array_oid = 1016 # INT8[] else: raise InterfaceError( "numeric not supported as array contents") else: try: oid, _ = self.make_param(first_element) # If unknown or string, assume it's a string array if oid in (705, 1043, 25): oid = 25 # Use binary ARRAY format to avoid having to properly # escape text in the array literals array_oid = converters.PG_ARRAY_TYPES[oid] except KeyError: raise InterfaceError("oid " + str(oid) + " not supported as array contents") return (array_oid, self.array_out)
def _send_message(self, code, data): try: self._write(code) self._write(i_pack(len(data) + 4)) self._write(data) except ValueError as e: if str(e) == "write to closed file": raise InterfaceError("connection is closed") else: raise e except AttributeError: raise InterfaceError("connection is closed")
def execute(self, operation, args=(), stream=None): """Executes a database operation. Parameters may be provided as a sequence, or as a mapping, depending upon the value of :data:`pg8000.paramstyle`. This method is part of the `DBAPI 2.0 specification <http://www.python.org/dev/peps/pep-0249/>`_. :param operation: The SQL statement to execute. :param args: If :data:`paramstyle` is ``qmark``, ``numeric``, or ``format``, this argument should be an array of parameters to bind into the statement. If :data:`paramstyle` is ``named``, the argument should be a dict mapping of parameters. If the :data:`paramstyle` is ``pyformat``, the argument value may be either an array or a mapping. :param stream: This is a pg8000 extension for use with the PostgreSQL `COPY <http://www.postgresql.org/docs/current/static/sql-copy.html>`_ command. For a COPY FROM the parameter must be a readable file-like object, and for COPY TO it must be writable. .. versionadded:: 1.9.11 """ try: if not self._c.in_transaction and not self._c.autocommit: self._c.execute_unnamed("begin transaction") statement, vals = convert_paramstyle(paramstyle, operation, args) self._context = self._c.execute_unnamed( statement, vals=vals, input_oids=self._input_oids, stream=stream) if self._context.rows is None: self._row_iter = None else: self._row_iter = iter(self._context.rows) self._input_oids = None except AttributeError as e: if self._c is None: raise InterfaceError("Cursor closed") elif self._c._sock is None: raise InterfaceError("connection is closed") else: raise e self.input_types = []
def send_QUERY(self, sql): data = sql.encode(self._client_encoding) + NULL_BYTE try: self._write(QUERY) self._write(i_pack(len(data) + 4)) self._write(data) except ValueError as e: if str(e) == "write to closed file": raise InterfaceError("connection is closed") else: raise e except AttributeError: raise InterfaceError("connection is closed")
def execute_unnamed(self, statement, vals=(), oids=(), stream=None): context = Context(stream=stream) self.send_PARSE(NULL_BYTE, statement, oids) self._write(SYNC_MSG) self._flush() self.handle_messages(context) self.send_DESCRIBE_STATEMENT(NULL_BYTE) self._write(SYNC_MSG) try: self._flush() except AttributeError as e: if self._sock is None: raise InterfaceError("connection is closed") else: raise e params = make_params(self.py_types, vals) self.send_BIND(NULL_BYTE, params) self.handle_messages(context) self.send_EXECUTE() self._write(SYNC_MSG) self._flush() self.handle_messages(context) return context
def prepare_statement(self, statement, oids): for i in count(): statement_name = "_".join(("pg8000", "statement", str(i))) statement_name_bin = statement_name.encode("ascii") + NULL_BYTE if statement_name_bin not in self._statement_nums: self._statement_nums.add(statement_name_bin) break self.send_PARSE(statement_name_bin, statement, oids) self.send_DESCRIBE_STATEMENT(statement_name_bin) self._write(SYNC_MSG) try: self._flush() except AttributeError as e: if self._sock is None: raise InterfaceError("connection is closed") else: raise e context = Context() self.handle_messages(context) return statement_name_bin, context.columns, context.input_funcs
def make_param(self, value): typ = type(value) try: oid, func = self.py_types[typ] except KeyError: try: oid, func = self.inspect_funcs[typ](value) except KeyError as e: oid, func = None, None for k, v in self.py_types.items(): try: if isinstance(value, k): oid, func = v break except TypeError: pass if oid is None: for k, v in self.inspect_funcs.items(): try: if isinstance(value, k): oid, func = v(value) break except TypeError: pass except KeyError: pass if oid is None: raise InterfaceError("type " + str(e) + " not mapped to pg type") return oid, func(value)
def handle_COPY_IN_RESPONSE(self, data, results): # Int16(2) - Number of columns # Int16(N) - Format codes for each column (0 text, 1 binary) is_binary, num_cols = bh_unpack(data) # column_formats = unpack_from('!' + 'h' * num_cols, data, 3) if results.stream is None: raise InterfaceError( "An input stream is required for the COPY IN response." ) bffr = bytearray(8192) while True: bytes_read = results.stream.readinto(bffr) if bytes_read == 0: break self._write(COPY_DATA + i_pack(bytes_read + 4)) self._write(bffr[:bytes_read]) self._flush() # Send CopyDone # Byte1('c') - Identifier. # Int32(4) - Message length, including self. self._write(COPY_DONE_MSG) self._write(SYNC_MSG) self._flush()
def handle_COPY_OUT_RESPONSE(self, data, ps): """https://www.postgresql.org/docs/current/protocol-message-formats.html""" is_binary, num_cols = bh_unpack(data) # column_formats = unpack_from('!' + 'h' * num_cols, data, 3) if ps.stream is None: raise InterfaceError( "An output stream is required for the COPY OUT response.")
def callproc(self, procname, parameters=None): args = [] if parameters is None else parameters operation = "CALL " + procname + "(" + ", ".join(["%s" for _ in args]) + ")" try: statement, vals = convert_paramstyle("format", operation, args) self._context = self._c.execute_unnamed(statement, vals=vals) self._row_iter = iter(self._context.rows) except AttributeError as e: if self._c is None: raise InterfaceError("Cursor closed") elif self._c._sock is None: raise InterfaceError("connection is closed") else: raise e
def array_inspect(array): first_element = None flattened = list(array_flatten(array)) for v in flattened: if v is not None: first_element = v break if first_element is None: oid = VARCHAR elif isinstance(first_element, bool): oid = BOOLEAN elif isinstance(first_element, int): int_oids = [SMALLINT, INTEGER, BIGINT, NUMERIC] for v in flattened: if v is None: continue v_oid = int_oid(v) try: oid_idx = int_oids.index(v_oid) except ValueError: continue if oid_idx > 0: int_oids = int_oids[oid_idx:] oid = int_oids[0] else: oid, _ = make_param(PY_TYPES, first_element) try: array_oid = PG_ARRAY_TYPES[oid] except KeyError: raise InterfaceError(f"oid {oid} not supported as array contents") try: return PY_TYPES[array_oid] except KeyError: raise InterfaceError(f"array oid {array_oid} not supported")
def close(self): """Closes the database connection. This function is part of the `DBAPI 2.0 specification <http://www.python.org/dev/peps/pep-0249/>`_. """ try: self._write(TERMINATE_MSG) self._flush() self._sock.close() except AttributeError: raise InterfaceError("connection is closed") except ValueError: raise InterfaceError("connection is closed") except socket.error: pass finally: self._usock.close() self._sock = None
def run(self, **vals): params = make_params(self.con.py_types, self.make_args(vals)) try: if not self.con.in_transaction and not self.con.autocommit: self.con.execute_unnamed("begin transaction") self._context = self.con.execute_named( self.name_bin, params, self.row_desc, self.input_funcs ) except AttributeError as e: if self.con is None: raise InterfaceError("Cursor closed") elif self.con._sock is None: raise InterfaceError("connection is closed") else: raise e return tuple() if self._context.rows is None else tuple(self._context.rows)
def make_vals(args): vals = [] for p in placeholders: try: vals.append(args[p]) except KeyError: raise InterfaceError( f"There's a placeholder '{p}' in the query, but no matching " f"keyword argument.") return tuple(vals)
def handle_COPY_OUT_RESPONSE(self, data, ps): # Int8(1) - 0 textual, 1 binary # Int16(2) - Number of columns # Int16(N) - Format codes for each column (0 text, 1 binary) is_binary, num_cols = bh_unpack(data) # column_formats = unpack_from('!' + 'h' * num_cols, data, 3) if ps.stream is None: raise InterfaceError( "An output stream is required for the COPY OUT response.")
def handle_COPY_IN_RESPONSE(self, data, context): """https://www.postgresql.org/docs/current/protocol-message-formats.html""" is_binary, num_cols = bh_unpack(data) # column_formats = unpack_from('!' + 'h' * num_cols, data, 3) if context.stream is None: raise InterfaceError( "An input stream is required for the COPY IN response.") elif isinstance(context.stream, TextIOBase): if is_binary: raise InterfaceError( "The COPY IN stream is binary, but the stream parameter is text." ) else: def ri(bffr): bffr.clear() bffr.extend( context.stream.read(4096).encode( self._client_encoding)) return len(bffr) readinto = ri else: readinto = context.stream.readinto bffr = bytearray(8192) while True: bytes_read = readinto(bffr) if bytes_read == 0: break self._write(COPY_DATA) self._write(i_pack(bytes_read + 4)) self._write(bffr[:bytes_read]) self._flush() # Send CopyDone self._write(COPY_DONE_MSG) self._write(SYNC_MSG) self._flush()
def close(self): """Closes the database connection. This function is part of the `DBAPI 2.0 specification <http://www.python.org/dev/peps/pep-0249/>`_. """ try: # Byte1('X') - Identifies the message as a terminate message. # Int32(4) - Message length, including self. self._write(TERMINATE_MSG) self._flush() self._sock.close() except AttributeError: raise InterfaceError("connection is closed") except ValueError: raise InterfaceError("connection is closed") except socket.error: pass finally: self._usock.close() self._sock = None
def identifier(sql): if not isinstance(sql, str): raise InterfaceError("identifier must be a str") if len(sql) == 0: raise InterfaceError("identifier must be > 0 characters in length") quote = not sql[0].isalpha() for c in sql[1:]: if not (c.isalpha() or c.isdecimal() or c in "_$"): if c == "\u0000": raise InterfaceError( "identifier cannot contain the code zero character") quote = True break if quote: sql = sql.replace('"', '""') return f'"{sql}"' else: return sql
def handle_messages(self, context): code = self.error = None while code != READY_FOR_QUERY: try: code, data_len = ci_unpack(self._read(5)) except struct.error as e: raise InterfaceError("network error on read") from e self.message_types[code](self._read(data_len - 4), context) if self.error is not None: raise self.error
def run(self, **vals): oids, params = make_params(self.con.py_types, self.make_args(vals)) try: name_bin, row_desc, input_funcs = self.name_map[oids] except KeyError: name_bin, row_desc, input_funcs = self.name_map[ oids] = self.con.prepare_statement(self.statement, oids) try: if not self.con.in_transaction and not self.con.autocommit: self.con.execute_unnamed("begin transaction") self._context = self.con.execute_named(name_bin, params, row_desc, input_funcs) except AttributeError as e: if self.con is None: raise InterfaceError("Cursor closed") elif self.con._sock is None: raise InterfaceError("connection is closed") else: raise e return tuple(self._context.rows)
def execute_unnamed(self, statement, vals=(), input_oids=None, stream=None): context = Context(stream=stream) if len(vals) == 0 and stream is None: self.send_QUERY(statement) self._flush() self.handle_messages(context) else: param_oids, params = make_params(self.py_types, vals) if input_oids is None: oids = param_oids else: oids = [(p if i is None else i) for p, i in zip(param_oids, input_oids)] self.send_PARSE(NULL_BYTE, statement, oids) self._write(SYNC_MSG) self._flush() self.handle_messages(context) self.send_DESCRIBE_STATEMENT(NULL_BYTE) self._write(SYNC_MSG) try: self._flush() except AttributeError as e: if self._sock is None: raise InterfaceError("connection is closed") else: raise e self.send_BIND(NULL_BYTE, params) self.handle_messages(context) self.send_EXECUTE() self._write(SYNC_MSG) self._flush() self.handle_messages(context) return context
def timedelta_in(data): t = {} curr_val = None for k in data.split(): if ':' in k: t['hours'], t['minutes'], t['seconds'] = map(float, k.split(':')) else: try: curr_val = float(k) except ValueError: t[PGInterval.UNIT_MAP[k]] = curr_val for n in ['weeks', 'months', 'years', 'decades', 'centuries', 'millennia']: if n in t: raise InterfaceError("Can't fit the interval " + str(t) + " into a datetime.timedelta.") return Timedelta(**t)
def interval_in(data): t = {} curr_val = None for k in data.split(): if ":" in k: t["hours"], t["minutes"], t["seconds"] = map(float, k.split(":")) else: try: curr_val = float(k) except ValueError: t[PGInterval.UNIT_MAP[k]] = curr_val for n in ["weeks", "months", "years", "decades", "centuries", "millennia"]: if n in t: raise InterfaceError( f"Can't fit the interval {t} into a datetime.timedelta.") return Timedelta(**t)
def handle_COPY_IN_RESPONSE(self, data, results): """https://www.postgresql.org/docs/current/protocol-message-formats.html""" is_binary, num_cols = bh_unpack(data) # column_formats = unpack_from('!' + 'h' * num_cols, data, 3) if results.stream is None: raise InterfaceError( "An input stream is required for the COPY IN response.") bffr = bytearray(8192) while True: bytes_read = results.stream.readinto(bffr) if bytes_read == 0: break self._write(COPY_DATA + i_pack(bytes_read + 4)) self._write(bffr[:bytes_read]) self._flush() # Send CopyDone self._write(COPY_DONE_MSG) self._write(SYNC_MSG) self._flush()
def handle_AUTHENTICATION_REQUEST(self, data, context): # Int32 - An authentication code that represents different # authentication messages: # 0 = AuthenticationOk # 5 = MD5 pwd # 2 = Kerberos v5 (not supported by pg8000) # 3 = Cleartext pwd # 4 = crypt() pwd (not supported by pg8000) # 6 = SCM credential (not supported by pg8000) # 7 = GSSAPI (not supported by pg8000) # 8 = GSSAPI data (not supported by pg8000) # 9 = SSPI (not supported by pg8000) # Some authentication messages have additional data following the # authentication code. That data is documented in the appropriate # class. auth_code = i_unpack(data)[0] if auth_code == 0: pass elif auth_code == 3: if self.password is None: raise InterfaceError( "server requesting password authentication, but no password was " "provided") self._send_message(PASSWORD, self.password + NULL_BYTE) self._flush() elif auth_code == 5: ## # A message representing the backend requesting an MD5 hashed # password response. The response will be sent as # md5(md5(pwd + login) + salt). # Additional message data: # Byte4 - Hash salt. salt = b"".join(cccc_unpack(data, 4)) if self.password is None: raise InterfaceError( "server requesting MD5 password authentication, but no password " "was provided") pwd = b"md5" + md5( md5(self.password + self.user).hexdigest().encode("ascii") + salt).hexdigest().encode("ascii") # Byte1('p') - Identifies the message as a password message. # Int32 - Message length including self. # String - The password. Password may be encrypted. self._send_message(PASSWORD, pwd + NULL_BYTE) self._flush() elif auth_code == 10: # AuthenticationSASL mechanisms = [ m.decode("ascii") for m in data[4:-2].split(NULL_BYTE) ] self.auth = scramp.ScramClient( mechanisms, self.user.decode("utf8"), self.password.decode("utf8"), channel_binding=self.channel_binding, ) init = self.auth.get_client_first().encode("utf8") mech = self.auth.mechanism_name.encode("ascii") + NULL_BYTE # SASLInitialResponse self._write( create_message(PASSWORD, mech + i_pack(len(init)) + init)) self._flush() elif auth_code == 11: # AuthenticationSASLContinue self.auth.set_server_first(data[4:].decode("utf8")) # SASLResponse msg = self.auth.get_client_final().encode("utf8") self._write(create_message(PASSWORD, msg)) self._flush() elif auth_code == 12: # AuthenticationSASLFinal self.auth.set_server_final(data[4:].decode("utf8")) elif auth_code in (2, 4, 6, 7, 8, 9): raise InterfaceError( f"Authentication method {auth_code} not supported by pg8000.") else: raise InterfaceError( f"Authentication method {auth_code} not recognized by pg8000.")
def sock_flush(): try: self._sock.flush() except OSError as e: raise InterfaceError("network error on flush") from e
def sock_write(d): try: self._sock.write(d) except OSError as e: raise InterfaceError("network error on write") from e
def sock_read(b): try: return self._sock.read(b) except OSError as e: raise InterfaceError("network error on read") from e