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
