def register_uuid(oids=None, conn_or_curs=None):
"""Create the UUID type and an uuid.UUID adapter."""
if not oids:
oid1 = 2950
oid2 = 2951
elif type(oids) == list:
oid1, oid2 = oids
else:
oid1 = oids
oid2 = 2951
def parseUUIDARRAY(data, cursor):
if data is None:
return None
elif data == "{}":
return []
else:
return [((len(x) > 0 and x != "NULL") and uuid.UUID(x) or None) for x in data[1:-1].split(",")]
_ext.UUID = _ext.new_type((oid1,), "UUID", lambda data, cursor: data and uuid.UUID(data) or None)
_ext.UUIDARRAY = _ext.new_type((oid2,), "UUID[]", parseUUIDARRAY)
_ext.register_type(_ext.UUID, conn_or_curs)
_ext.register_type(_ext.UUIDARRAY, conn_or_curs)
_ext.register_adapter(uuid.UUID, UUID_adapter)
return _ext.UUID
def _make_casters():
inet = new_type((869,), 'INET', cast_interface)
ainet = new_array_type((1041,), 'INET[]', inet)
cidr = new_type((650,), 'CIDR', cast_network)
acidr = new_array_type((651,), 'CIDR[]', cidr)
return [inet, ainet, cidr, acidr]
def register_uuid(oids=None, conn_or_curs=None):
"""Create the UUID type and an uuid.UUID adapter.
:param oids: oid for the PostgreSQL :sql:`uuid` type, or 2-items sequence
with oids of the type and the array. If not specified, use PostgreSQL
standard oids.
:param conn_or_curs: where to register the typecaster. If not specified,
register it globally.
"""
import uuid
if not oids:
oid1 = 2950
oid2 = 2951
elif isinstance(oids, (list, tuple)):
oid1, oid2 = oids
else:
oid1 = oids
oid2 = 2951
_ext.UUID = _ext.new_type((oid1, ), "UUID",
lambda data, cursor: data and uuid.UUID(data) or None)
_ext.UUIDARRAY = _ext.new_array_type((oid2,), "UUID[]", _ext.UUID)
_ext.register_type(_ext.UUID, conn_or_curs)
_ext.register_type(_ext.UUIDARRAY, conn_or_curs)
_ext.register_adapter(uuid.UUID, UUID_adapter)
return _ext.UUID
def register_ltree(conn_or_curs, globally=False, oid=None, array_oid=None):
"""Register the ltree adapter and typecaster on the connection or cursor.
"""
register_adapter()
conn, curs, conn_or_curs = _solve_conn_curs(conn_or_curs)
if oid is None:
oid = get_oids(conn_or_curs, 'ltree')
if oid is None or not oid[0]:
raise psycopg2.ProgrammingError(
"ltree type not found in the database."
)
else:
array_oid = oid[1]
oid = oid[0]
if isinstance(oid, int):
oid = (oid,)
if array_oid is not None:
if isinstance(array_oid, int):
array_oid = (array_oid,)
else:
array_oid = tuple([x for x in array_oid if x])
# create and register the typecaster
LTREE = ext.new_type(oid, "LTREE", cast_ltree)
ext.register_type(LTREE, not globally and conn_or_curs or None)
if array_oid:
LTREEARRAY = ext.new_array_type(array_oid, "LTREEARRAY", LTREE)
ext.register_type(LTREEARRAY, not globally and conn_or_curs or None)
def register_extensions(con):
pgext.register_adapter(Polygon, adapt_wkt)
pgext.register_adapter(Point, adapt_wkt)
pgext.register_adapter(LineString, adapt_wkt)
with con.cursor() as c:
c.execute('select null::point, null::polygon, null::geometry')
point_oid, poly_oid = c.description[0][1], c.description[1][1]
geom_oid = c.description[2][1]
point = pgext.new_type((point_oid,), 'POINT', cast_wkb)
polygon = pgext.new_type((poly_oid,), 'POLYGON', cast_wkb)
geom = pgext.new_type((geom_oid,), 'GEOMETRY', cast_wkb)
pgext.register_type(point)
pgext.register_type(polygon)
pgext.register_type(geom)
def register_inet(oid=None, conn_or_curs=None):
"""Create the INET type and an Inet adapter.
:param oid: oid for the PostgreSQL :sql:`inet` type, or 2-items sequence
with oids of the type and the array. If not specified, use PostgreSQL
standard oids.
:param conn_or_curs: where to register the typecaster. If not specified,
register it globally.
"""
import warnings
warnings.warn(
"the inet adapter is deprecated, it's not very useful",
DeprecationWarning)
if not oid:
oid1 = 869
oid2 = 1041
elif isinstance(oid, (list, tuple)):
oid1, oid2 = oid
else:
oid1 = oid
oid2 = 1041
_ext.INET = _ext.new_type((oid1, ), "INET",
lambda data, cursor: data and Inet(data) or None)
_ext.INETARRAY = _ext.new_array_type((oid2, ), "INETARRAY", _ext.INET)
_ext.register_type(_ext.INET, conn_or_curs)
_ext.register_type(_ext.INETARRAY, conn_or_curs)
return _ext.INET
def register_tstz_w_secs(oids=None, conn_or_curs=None):
"""Register alternate type caster for TIMESTAMP WITH TIME ZONE.
The Python datetime module cannot handle time zones with
seconds in the UTC offset. There are, however, historical
"time zones" which contain such offsets, eg. "Asia/Calcutta".
In many cases those offsets represent true local time.
If you encounter "unable to parse time" on a perfectly valid
timestamp you likely want to try this type caster. It truncates
the seconds from the time zone data and retries casting
the timestamp. Note that this will generate timestamps
which are INACCURATE by the number of seconds truncated
(unless the seconds were 00).
<oids>
which OIDs to use this type caster for,
defaults to TIMESTAMP WITH TIME ZONE
<conn_or_curs>
a cursor or connection if you want to attach
this type caster to that only, defaults to
None meaning all connections and cursors
"""
if oids is None:
oids = (1184,) # hardcoded from PostgreSQL headers
_ext.TSTZ_W_SECS = _ext.new_type(oids, "TSTZ_W_SECS", _convert_tstz_w_secs)
_ext.register_type(TSTZ_W_SECS, conn_or_curs)
return _ext.TSTZ_W_SECS
def register_inet(oid=None, conn_or_curs=None):
"""Create the INET type and an Inet adapter."""
if not oid:
oid = 869
_ext.INET = _ext.new_type((oid,), "INET", lambda data, cursor: data and Inet(data) or None)
_ext.register_type(_ext.INET, conn_or_curs)
return _ext.INET
def register_ltree(conn):
oid = _get_ltree_oids(conn)
if not oid[0]:
return False
else:
array_oid = oid[1]
oid = oid[0]
if isinstance(oid, int):
oid = (oid,)
if array_oid is not None:
if isinstance(array_oid, int):
array_oid = (array_oid,)
else:
array_oid = tuple([x for x in array_oid if x])
ltree = extensions.new_type(oid, "LTREE", _cast_fn)
extensions.register_type(ltree, None)
if array_oid:
ltree_array = extensions.new_array_type(array_oid, "LTREEARRAY", ltree)
extensions.register_type(ltree_array, None)
return True
def register_cidr(oid=None, conn_or_curs=None):
"""Create the CIDR type and an Cidr adapter.
:param oid: oid for the PostgreSQL :sql:`cidr` type, or 2-items sequence
with oids of the type and the array. If not specified, use PostgreSQL
standard oids.
:param conn_or_curs: where to register the typecaster. If not specified,
register it globally.
"""
if not oid:
oid1 = 650
oid2 = 651
elif isinstance(oid, (list, tuple)):
oid1, oid2 = oid
else:
oid1 = oid
oid2 = 651
_ext.CIDR = _ext.new_type((oid1, ), "CIDR",
lambda data, cursor: data and Cidr(data) or None)
_ext.CIDRARRAY = _ext.new_array_type((oid2, ), "CIDRARRAY", _ext.CIDR)
_ext.register_type(_ext.CIDR, conn_or_curs)
_ext.register_type(_ext.CIDRARRAY, conn_or_curs)
return _ext.CIDR
def register_uuid(oid=None, conn_or_curs=None):
"""Create the UUID type and an uuid.UUID adapter."""
if not oid: oid = 2950
_ext.UUID = _ext.new_type((oid, ), "UUID",
lambda data, cursor: data and uuid.UUID(data) or None)
_ext.register_type(_ext.UUID, conn_or_curs)
_ext.register_adapter(uuid.UUID, UUID_adapter)
return _ext.UUID
def _register_inet(oid=None, conn_or_curs=None):
"""Create the INET type and an Inet adapter."""
from psycopg2 import extensions as _ext
if not oid: oid = 869
_ext.INET = _ext.new_type((oid, ), "INET",
lambda data, cursor: data and Inet(data) or None)
_ext.register_type(_ext.INET, conn_or_curs)
return _ext.INET
def __init__(self, name, oid, attrs):
self.name = name
self.oid = oid
self.attnames = [ a[0] for a in attrs ]
self.atttypes = [ a[1] for a in attrs ]
self._create_type(name, self.attnames)
self.typecaster = _ext.new_type((oid,), name, self.parse)
def register_cast(cls, connection):
cast_function = CAST_MAPPER[cls.type_name()]
cursor = connection.cursor()
cursor.execute(cls.sql_for_oid())
oid = cursor.description[0][1]
cursor.close()
PGTYPE = new_type((oid,), cls.type_name().upper(), cast_function)
register_type(PGTYPE)
def register_range_caster(pgrange, pyrange, oid, subtype_oid, array_oid, scope=None):
# Create an adapter for this range type
adapter = partial(cast_raw_range_string, pyrange, subtype_oid=subtype_oid)
# Create types using adapter
range_type = new_type((oid,), pgrange, adapter)
range_array_type = new_array_type((array_oid,), pgrange, range_type)
register_type(range_type, scope)
register_type(range_array_type, scope)
def register_citext_type(dbapi_con, connection_record):
def cast_citext(in_str, cursor):
if in_str == None:
return None
return unicode(in_str, cursor.connection.encoding)
with closing(dbapi_con.cursor()) as c:
c.execute(b"SELECT pg_type.oid FROM pg_type WHERE typname = 'citext'")
citext_oid = c.fetchone()
if citext_oid != None:
citext_type = new_type(citext_oid, b"CITEXT", cast_citext)
register_type(citext_type)
def register_hstore(conn_or_curs, globally=False, unicode=False, oid=None):
"""Register adapter and typecaster for `!dict`\-\ |hstore| conversions.
:param conn_or_curs: a connection or cursor: the typecaster will be
registered only on this object unless *globally* is set to `!True`
:param globally: register the adapter globally, not only on *conn_or_curs*
:param unicode: if `!True`, keys and values returned from the database
will be `!unicode` instead of `!str`. The option is not available on
Python 3
:param oid: the OID of the |hstore| type if known. If not, it will be
queried on *conn_or_curs*
The connection or cursor passed to the function will be used to query the
database and look for the OID of the |hstore| type (which may be different
across databases). If querying is not desirable (e.g. with
:ref:`asynchronous connections <async-support>`) you may specify it in the
*oid* parameter (it can be found using a query such as :sql:`SELECT
'hstore'::regtype::oid;`).
Note that, when passing a dictionary from Python to the database, both
strings and unicode keys and values are supported. Dictionaries returned
from the database have keys/values according to the *unicode* parameter.
The |hstore| contrib module must be already installed in the database
(executing the ``hstore.sql`` script in your ``contrib`` directory).
Raise `~psycopg2.ProgrammingError` if the type is not found.
.. versionchanged:: 2.4
added the *oid* parameter. If not specified, the typecaster is
installed also if |hstore| is not installed in the :sql:`public`
schema.
"""
if oid is None:
oid = HstoreAdapter.get_oids(conn_or_curs)
if oid is None or not oid[0]:
raise psycopg2.ProgrammingError(
"hstore type not found in the database. "
"please install it from your 'contrib/hstore.sql' file")
else:
oid = oid[0] # for the moment we don't have a HSTOREARRAY
if isinstance(oid, int):
oid = (oid,)
# create and register the typecaster
if sys.version_info[0] < 3 and unicode:
cast = HstoreAdapter.parse_unicode
else:
cast = HstoreAdapter.parse
HSTORE = _ext.new_type(oid, "HSTORE", cast)
_ext.register_type(HSTORE, not globally and conn_or_curs or None)
_ext.register_adapter(dict, HstoreAdapter)
def register_bitstring_types(connection):
"""Register the BIT and VARBIT casters on the provided connection.
This ensures that BIT and VARBIT instances returned from the database will
be represented in Python as ``bitstring.Bits`` instances.
"""
with closing(connection.cursor()) as cur:
cur.execute("SELECT NULL::BIT")
bit_oid = cur.description[0].type_code
cur.execute("SELECT NULL::VARBIT")
varbit_oid = cur.description[0].type_code
bit_caster = ext.new_type((bit_oid, varbit_oid), 'BIT', cast_bits)
def register_macaddr_type():
from psycopg2.extensions import register_adapter, new_type, register_type, new_array_type
import psycopg2
oid = get_type_oid("NULL::macaddr")
PGTYPE = new_type((oid,), "macaddr", cast_macaddr)
register_type(PGTYPE)
register_adapter(MacAddr, adapt_macaddr)
mac_array_oid = get_type_oid("'{}'::macaddr[]")
array_of_mac = new_array_type((mac_array_oid, ), 'macaddr', psycopg2.STRING)
psycopg2.extensions.register_type(array_of_mac)
def __init__(self, pgrange, pyrange, oid, subtype_oid, array_oid=None):
self.subtype_oid = subtype_oid
self._create_ranges(pgrange, pyrange)
name = self.adapter.name or self.adapter.__class__.__name__
self.typecaster = new_type((oid,), name, self.parse)
if array_oid is not None:
self.array_typecaster = new_array_type((array_oid,), name + "ARRAY", self.typecaster)
else:
self.array_typecaster = None
def _bind_type(conn, sql_type_name, cast):
"""
Binds a `cast` function to the SQL type in the connection `conn`
given by `sql_type_name`. `cast` must be a function with two
parameters: the SQL value and a cursor object. It should return the
appropriate Python object.
Note that `sql_type_name` is not escaped.
"""
with Tx(conn) as c:
c.execute('SELECT NULL::%s' % sql_type_name)
typ = new_type((c.description[0].type_code,), sql_type_name, cast)
register_type(typ)
def register_hstore_typecaster(conn):
"""
Instead of using register_hstore() which converts hstore into a python
dict, we query the 'oid' of hstore which will be different for each
database and register a type caster that converts it to unicode.
http://initd.org/psycopg/docs/extras.html#psycopg2.extras.register_hstore
"""
with conn.cursor() as cur:
try:
cur.execute("SELECT 'hstore'::regtype::oid")
oid = cur.fetchone()[0]
ext.register_type(ext.new_type((oid,), "HSTORE", ext.UNICODE))
except Exception:
pass
def __init__(self, name, oid, attrs, array_oid=None):
self.name = name
self.oid = oid
self.array_oid = array_oid
self.attnames = [ a[0] for a in attrs ]
self.atttypes = [ a[1] for a in attrs ]
self._create_type(name, self.attnames)
self.typecaster = new_type((oid,), name, self.parse)
if array_oid:
self.array_typecaster = new_array_type(
(array_oid,), "%sARRAY" % name, self.typecaster)
else:
self.array_typecaster = None
def setup_connection(conn):
# We want to use unicode everywhere
register_type(UNICODE, conn)
register_type(UNICODEARRAY, conn)
cur = conn.cursor()
cur.execute("SELECT NULL::numeric")
oid = cur.description[0][1]
NUMERIC = new_type((oid,), "NUMERIC", numeric_converter)
register_type(NUMERIC, conn)
register_adapter(Integer, AsIs)
register_adapter(RealNumber, RealEncoder)
register_adapter(list, Json)
register_adapter(tuple, Json)
register_adapter(dict, Json)
register_json(conn, loads=Json.loads)
def register_hstore(conn_or_curs, globally=False, unicode=False,
oid=None, array_oid=None):
from psycopg2.extras import HstoreAdapter
from psycopg2 import extensions as _ext
import psycopg2
import sys
import re as regex
from .fields import HStoreDict
def cast(s, cur, _bsdec=regex.compile(r"\\(.)")):
if sys.version_info[0] < 3 and unicode:
result = HstoreAdapter.parse_unicode(s, cur)
else:
result = HstoreAdapter.parse(s, cur, _bsdec)
return HStoreDict(result)
if oid is None:
oid = HstoreAdapter.get_oids(conn_or_curs)
if oid is None or not oid[0]:
raise psycopg2.ProgrammingError(
"hstore type not found in the database. "
"please install it from your 'contrib/hstore.sql' file")
else:
array_oid = oid[1]
oid = oid[0]
if isinstance(oid, int):
oid = (oid,)
if array_oid is not None:
if isinstance(array_oid, int):
array_oid = (array_oid,)
else:
array_oid = tuple([x for x in array_oid if x])
HSTORE = _ext.new_type(oid, str("HSTORE"), cast)
_ext.register_type(HSTORE, not globally and conn_or_curs or None)
_ext.register_adapter(dict, HstoreAdapter)
if array_oid:
HSTOREARRAY = _ext.new_array_type(array_oid, str("HSTOREARRAY"), HSTORE)
_ext.register_type(HSTOREARRAY, not globally and conn_or_curs or None)
import psycopg2.errorcodes
import psycopg2.extensions as ext
import sqlparse
import pgspecial as special
import select
from psycopg2.extensions import POLL_OK, POLL_READ, POLL_WRITE
from .packages.parseutils.meta import FunctionMetadata, ForeignKey
from .encodingutils import unicode2utf8, PY2, utf8tounicode
_logger = logging.getLogger(__name__)
# Cast all database input to unicode automatically.
# See http://initd.org/psycopg/docs/usage.html#unicode-handling for more info.
ext.register_type(ext.UNICODE)
ext.register_type(ext.UNICODEARRAY)
ext.register_type(ext.new_type((705,), "UNKNOWN", ext.UNICODE))
# See https://github.com/dbcli/pgcli/issues/426 for more details.
# This registers a unicode type caster for datatype 'RECORD'.
ext.register_type(ext.new_type((2249,), "RECORD", ext.UNICODE))
# Cast bytea fields to text. By default, this will render as hex strings with
# Postgres 9+ and as escaped binary in earlier versions.
ext.register_type(ext.new_type((17,), 'BYTEA_TEXT', psycopg2.STRING))
# TODO: Get default timeout from pgclirc?
_WAIT_SELECT_TIMEOUT = 1
def _wait_select(conn):
"""
copy-pasted from psycopg2.extras.wait_select
import sys
import numpy
# import pyodbc # jun commented this out to work around pyodbc when importing DBClient from sq_sql, don't git add this!
import pandas
import datetime
import json
import psycopg2
import string
import re
import numpy
from sq_psql import make_psql_compatible_column, double_quote_name
import psycopg2.extensions as ext
ext.register_type(psycopg2.extensions.UNICODE)
ext.register_type(psycopg2.extensions.UNICODEARRAY)
ext.register_type(ext.new_type((705, ), "UNKNOWN", ext.UNICODE))
# https://github.com/psycopg/psycopg2/issues/282 <-- 'unknown' type codes will not be cast to unicode by default
# http://initd.org/psycopg/docs/usage.html#unicode-handling -- Note that this is only required in Python 2.7
try:
from collections import OrderedDict
except ImportError:
from ordereddict import OrderedDict
# # Support pandas v0.11-current
try:
from pandas.util.py3compat import lzip # pandas 0.11
except ImportError:
from pandas.compat import lzip # pandas 0.12 and 0.13
## 10-16-2014: (BD) Works in current Py 2.6 on etl-dev
return iso
else:
return DateTime(
time.strftime(
"%Y-%m-%d %H:%M:%S", time.localtime(time.time())[:3] + time.strptime(iso[:8], "%H:%M:%S")[3:]
)
)
# NOTE: we don't cast intervals anymore because they are passed
# untouched to Zope.
def _cast_Interval(iso, curs):
return iso
ZDATETIME = new_type((1184, 1114), "ZDATETIME", _cast_DateTime)
ZINTERVAL = new_type((1186,), "ZINTERVAL", _cast_Interval)
ZDATE = new_type((1082,), "ZDATE", _cast_Date)
ZTIME = new_type((1083,), "ZTIME", _cast_Time)
## table browsing helpers ##
class TableBrowserCollection(Acquisition.Implicit):
pass
class Browser(Base):
def __getattr__(self, name):
try:
def register(connection):
if isinstance(connection, extensions.cursor):
# Retrocompat.
cursor = connection
else:
cursor = connection.cursor()
# Add MobilityDB types to PostgreSQL adapter and specify the reader function for each type.
cursor.execute("SELECT NULL::TimestampSet")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "TimestampSet",
TimestampSet.read_from_cursor))
cursor.execute("SELECT NULL::Period")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "Period", Period.read_from_cursor))
cursor.execute("SELECT NULL::PeriodSet")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "PeriodSet", PeriodSet.read_from_cursor))
cursor.execute("SELECT NULL::TBOX")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "TBOX", TBox.read_from_cursor))
cursor.execute("SELECT NULL::TBool")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "TBool", TBool.read_from_cursor))
cursor.execute("SELECT NULL::TInt")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "TInt", TInt.read_from_cursor))
cursor.execute("SELECT NULL::TFloat")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "TFloat", TFloat.read_from_cursor))
cursor.execute("SELECT NULL::TText")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "TText", TText.read_from_cursor))
cursor.execute("SELECT NULL::STBOX")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "STBOX", STBox.read_from_cursor))
cursor.execute("SELECT NULL::TGeomPoint")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "TGeomPoint",
TGeomPoint.read_from_cursor))
cursor.execute("SELECT NULL::TGeogPoint")
oid = cursor.description[0][1]
extensions.register_type(
extensions.new_type((oid, ), "TGeogPoint",
TGeogPoint.read_from_cursor))
Py2/3 compatibility layer
Mostly copied straight from six:
http://pypi.python.org/pypi/six/
"""
from __future__ import absolute_import
import psycopg2
from psycopg2 import extensions
import json
# If psycopg2 < 2.5, register json type
psycopg2_version = tuple(psycopg2.__version__.split(' ')[0].split('.'))
if psycopg2_version < ('2', '5'):
JSON_OID = 114
newtype = extensions.new_type(
(JSON_OID,), "JSON", lambda data, cursor: json.loads(data))
extensions.register_type(newtype)
def with_metaclass(meta, *bases):
"""Create a base class with a metaclass."""
# This requires a bit of explanation: the basic idea is to make a dummy
# metaclass for one level of class instantiation that replaces itself with
# the actual metaclass.
class metaclass(meta):
"""The actual metaclass."""
def __new__(cls, name, _, d):
return meta(name, bases, d)
return type.__new__(metaclass, 'temporary_class', (), {})
class classproperty(object):
"""
import psycopg2.errorcodes
import psycopg2.extensions as ext
import sqlparse
import pgspecial as special
import select
from psycopg2.extensions import POLL_OK, POLL_READ, POLL_WRITE
from .packages.parseutils.meta import FunctionMetadata, ForeignKey
from .encodingutils import unicode2utf8, PY2, utf8tounicode
_logger = logging.getLogger(__name__)
# Cast all database input to unicode automatically.
# See http://initd.org/psycopg/docs/usage.html#unicode-handling for more info.
ext.register_type(ext.UNICODE)
ext.register_type(ext.UNICODEARRAY)
ext.register_type(ext.new_type((705, ), "UNKNOWN", ext.UNICODE))
# See https://github.com/dbcli/pgcli/issues/426 for more details.
# This registers a unicode type caster for datatype 'RECORD'.
ext.register_type(ext.new_type((2249, ), "RECORD", ext.UNICODE))
# Cast bytea fields to text. By default, this will render as hex strings with
# Postgres 9+ and as escaped binary in earlier versions.
ext.register_type(ext.new_type((17, ), 'BYTEA_TEXT', psycopg2.STRING))
# TODO: Get default timeout from pgclirc?
_WAIT_SELECT_TIMEOUT = 1
_TRANSACTIONLESS = re.compile('^\s*transactionless\s+', re.I)
def _wait_select(conn):
def database(env, tell_sentry):
dburl = env.database_url
maxconn = env.database_maxconn
try:
db = DB(dburl, maxconn=maxconn)
except psycopg2.OperationalError as e:
tell_sentry(e, {})
pg_dir = os.environ.get('OPENSHIFT_PG_DATA_DIR')
if pg_dir:
# We know where the postgres data is, try to start the server ourselves
r = call(['pg_ctl', '-D', pg_dir, 'start', '-w', '-t', '15'])
if r == 0:
return database(env, tell_sentry)
db = NoDB()
models = (
_AccountElsewhere, AccountElsewhere, _Community, Community,
ExchangeRoute, Participant, Repository,
)
for model in models:
db.register_model(model)
liberapay.billing.payday.Payday.db = db
def adapt_money(m):
return AsIs('(%s,%s)::currency_amount' % (adapt(m.amount), adapt(m.currency)))
register_adapter(Money, adapt_money)
def cast_currency_amount(v, cursor):
return None if v in (None, '(,)') else Money(*v[1:-1].split(','))
try:
oid = db.one("SELECT 'currency_amount'::regtype::oid")
register_type(new_type((oid,), _str('currency_amount'), cast_currency_amount))
except psycopg2.ProgrammingError:
pass
def adapt_money_basket(b):
return AsIs(
"_wrap_amounts('%s'::jsonb)" %
json.dumps({k: str(v) for k, v in b.amounts.items() if v}).replace("'", "''")
)
register_adapter(MoneyBasket, adapt_money_basket)
def cast_currency_basket(v, cursor):
if v is None:
return None
parts = v[1:-1].split(',', 2)
if len(parts) == 2:
eur, usd = parts
obj = None
else:
eur, usd, obj = parts
if obj:
amounts = json.loads(obj[1:-1].replace('""', '"') if obj[0] == '"' else obj)
amounts = {k: Decimal(str(v)) for k, v in amounts.items()}
else:
amounts = {}
if eur:
amounts['EUR'] = Decimal(eur)
if usd:
amounts['USD'] = Decimal(usd)
return MoneyBasket(**amounts)
try:
oid = db.one("SELECT 'currency_basket'::regtype::oid")
register_type(new_type((oid,), _str('currency_basket'), cast_currency_basket))
except psycopg2.ProgrammingError:
pass
use_qc = not env.override_query_cache
qc1 = QueryCache(db, threshold=(1 if use_qc else 0))
qc5 = QueryCache(db, threshold=(5 if use_qc else 0))
return {'db': db, 'db_qc1': qc1, 'db_qc5': qc5}
def register_hstore(conn_or_curs,
globally=False,
str=False,
oid=None,
array_oid=None):
r"""Register adapter and typecaster for `!dict`\-\ |hstore| conversions.
:param conn_or_curs: a connection or cursor: the typecaster will be
registered only on this object unless *globally* is set to `!True`
:param globally: register the adapter globally, not only on *conn_or_curs*
:param unicode: if `!True`, keys and values returned from the database
will be `!unicode` instead of `!str`. The option is not available on
Python 3
:param oid: the OID of the |hstore| type if known. If not, it will be
queried on *conn_or_curs*.
:param array_oid: the OID of the |hstore| array type if known. If not, it
will be queried on *conn_or_curs*.
The connection or cursor passed to the function will be used to query the
database and look for the OID of the |hstore| type (which may be different
across databases). If querying is not desirable (e.g. with
:ref:`asynchronous connections <async-support>`) you may specify it in the
*oid* parameter, which can be found using a query such as :sql:`SELECT
'hstore'::regtype::oid`. Analogously you can obtain a value for *array_oid*
using a query such as :sql:`SELECT 'hstore[]'::regtype::oid`.
Note that, when passing a dictionary from Python to the database, both
strings and unicode keys and values are supported. Dictionaries returned
from the database have keys/values according to the *unicode* parameter.
The |hstore| contrib module must be already installed in the database
(executing the ``hstore.sql`` script in your ``contrib`` directory).
Raise `~psycopg2.ProgrammingError` if the type is not found.
"""
if oid is None:
oid = HstoreAdapter.get_oids(conn_or_curs)
if oid is None or not oid[0]:
raise psycopg2.ProgrammingError(
"hstore type not found in the database. "
"please install it from your 'contrib/hstore.sql' file")
else:
array_oid = oid[1]
oid = oid[0]
if isinstance(oid, int):
oid = (oid, )
if array_oid is not None:
if isinstance(array_oid, int):
array_oid = (array_oid, )
else:
array_oid = tuple([x for x in array_oid if x])
# create and register the typecaster
if _sys.version_info[0] < 3 and str:
cast = HstoreAdapter.parse_unicode
else:
cast = HstoreAdapter.parse
HSTORE = _ext.new_type(oid, "HSTORE", cast)
_ext.register_type(HSTORE, not globally and conn_or_curs or None)
_ext.register_adapter(dict, HstoreAdapter)
if array_oid:
HSTOREARRAY = _ext.new_array_type(array_oid, "HSTOREARRAY", HSTORE)
_ext.register_type(HSTOREARRAY, not globally and conn_or_curs or None)
def register_cast(oid, typename, method):
new_t = new_type((oid, ), typename, method)
register_type(new_t)
def register(cursor):
cursor.execute("SELECT NULL::geometry")
oid = cursor.description[0][1]
GEOMETRY = _ext.new_type((oid, ), "GEOMETRY", Geometry.from_ewkb)
_ext.register_type(GEOMETRY)
# http://rehalcon.blogspot.com/2010/03/sqlalchemy-programmingerror-cant-adapt.html
# and
# http://initd.org/psycopg/docs/advanced.html#adapting-new-python-types-to-sql-syntax
# and
# http://pyopengl.sourceforge.net/pydoc/numpy.core.numerictypes.html
#
# http://numpy.sourceforge.net/numdoc/HTML/numdoc.htm
''' numpy data types:
int8 int16 int32 int64 int128
uint8 uint16 uint32 uint64 uint128
float16 float32 float64 float96 float128 float256
complex32 complex64 complex128 complex192 complex256 complex512
'''
DEC2FLOAT = new_type(DECIMAL.values, 'DEC2FLOAT', lambda value,
curs: float(value) if value is not None else None)
register_type(DEC2FLOAT)
def adapt_decimal(Decimal):
return AsIs(float)
register_adapter(Decimal, adapt_decimal)
def adapt_numpy_int8(numpy_int8):
return AsIs(numpy_int8)
register_adapter(numpy.int8, adapt_numpy_int8)
def adapt_numpy_int16(numpy_int16):
return AsIs(numpy_int16)
return OilTimestamp(time.time())
def adaptOilTimestamp(oil_timestamp: OilTimestamp) -> AsIs:
return AsIs(str(oil_timestamp.ots))
def castOilTimestamp(value: Optional[str],
curs: 'psycopg2.cursor') -> Optional[OilTimestamp]:
if value is None: return None
return OilTimestamp.fromOil(int(value))
register_adapter(OilTimestamp, adaptOilTimestamp)
_OilTimestamp = new_type(
(getTypeOid('oil_timestamp'), ), "_OilTimestamp", castOilTimestamp
)
register_type(_OilTimestamp)
def oil_timestamp() -> OilTimestamp:
return OilTimestamp(time.time())
columnTypes = {
'boolean': 'bool',
'smallint': 'int',
'integer': 'int',
'bigint': 'int',
'serial': 'int',
'bigserial': 'int',
from psycopg2 import extensions as _ext
from agensgraph._graphid import (GraphId, cast_graphid as _cast_graphid,
adapt_graphid as _adapt_graphid)
from agensgraph._vertex import Vertex, cast_vertex as _cast_vertex
from agensgraph._edge import Edge, cast_edge as _cast_edge
from agensgraph._graphpath import Path, cast_graphpath as _cast_graphpath
from agensgraph._property import Property
_GRAPHID_OID = 7002
_VERTEX_OID = 7012
_EDGE_OID = 7022
_GRAPHPATH_OID = 7032
GRAPHID = _ext.new_type((_GRAPHID_OID, ), 'GRAPHID', _cast_graphid)
_ext.register_type(GRAPHID)
_ext.register_adapter(GraphId, _adapt_graphid)
VERTEX = _ext.new_type((_VERTEX_OID, ), 'VERTEX', _cast_vertex)
_ext.register_type(VERTEX)
EDGE = _ext.new_type((_EDGE_OID, ), 'EDGE', _cast_edge)
_ext.register_type(EDGE)
PATH = _ext.new_type((_GRAPHPATH_OID, ), 'PATH', _cast_graphpath)
_ext.register_type(PATH)
__all__ = [
'GraphId', 'Vertex', 'Edge', 'Path', 'Property', 'GRAPHID', 'VERTEX',
'EDGE', 'PATH'
return iso
else:
return DateTime(
time.strftime(
'%Y-%m-%d %H:%M:%S',
time.localtime(time.time())[:3] +
time.strptime(iso[:8], "%H:%M:%S")[3:]))
# NOTE: we don't cast intervals anymore because they are passed
# untouched to Zope.
def _cast_Interval(iso, curs):
return iso
ZDATETIME = new_type((1184, 1114), "ZDATETIME", _cast_DateTime)
ZINTERVAL = new_type((1186, ), "ZINTERVAL", _cast_Interval)
ZDATE = new_type((1082, ), "ZDATE", _cast_Date)
ZTIME = new_type((1083, ), "ZTIME", _cast_Time)
## table browsing helpers ##
class TableBrowserCollection(Acquisition.Implicit):
pass
class Browser(Base):
def __getattr__(self, name):
try:
return self._d[name]
with self.db.session.begin(subtransactions=True):
return fn(self, *args, **kwargs)
return wrapper
class Int4RangeType (UserDefinedType):
def __init__(self):
self.caster = RangeCaster('int4range', 'Int4Range', None, None)
def get_col_spec(self):
return 'INT4RANGE'
def bind_processor(self, dialect):
def process(value):
if value:
return AsIs(self.caster.range(value[0], value[1], '[)'))
return process
def result_processor(self, dialect, coltype):
def process(value):
if value is not None:
return (value.lower, value.upper)
return process
register_type(new_type((1082,), 'DATE', STRING))
ischema_names['int4range'] = Int4RangeType
# vim:set sw=4 ts=4 et:
except AttributeError:
from psycopg2.extensions import new_type, new_array_type, register_type, \
register_adapter, QuotedString
def cast_interface(s, cur=None):
if s is None:
return None
return ipaddress.ip_interface(unicode(s))
def cast_ipnetwork(s, cur=None):
if s is None:
return None
return ipaddress.ip_network(unicode(s))
inet = new_type((869,), b'INET', cast_interface)
ainet = new_array_type((1041,), b'INET[]', inet)
cidr = new_type((650,), b'CIDR', cast_ipnetwork)
acidr = new_array_type((651,), b'CIDR[]', cidr)
for c in [inet, ainet, cidr, acidr]:
register_type(c, None)
def adapt_ipaddress(obj):
return QuotedString(str(obj))
for t in [ipaddress.IPv4Interface, ipaddress.IPv6Interface,
ipaddress.IPv4Network, ipaddress.IPv6Network]:
register_adapter(t, adapt_ipaddress)
def database(env, tell_sentry):
dburl = env.database_url
maxconn = env.database_maxconn
try:
db = DB(dburl, maxconn=maxconn, cursor_factory=SimpleRowCursor)
except psycopg2.OperationalError as e:
tell_sentry(e, {}, allow_reraise=False)
db = NoDB()
itemgetter0 = itemgetter(0)
def back_as_Object(cols, vals):
return Object(zip(map(itemgetter0, cols), vals))
db.back_as_registry[Object] = db.back_as_registry['Object'] = back_as_Object
models = (
_AccountElsewhere, AccountElsewhere, _Community, Community,
Encrypted, ExchangeRoute, Participant, Payin, Repository, Tip,
)
for model in models:
db.register_model(model)
setattr(db, model.__name__, model)
liberapay.billing.payday.Payday.db = db
def adapt_set(s):
return adapt(tuple(s))
register_adapter(set, adapt_set)
def adapt_money(m):
return AsIs('(%s,%s)::currency_amount' % (adapt(m.amount), adapt(m.currency)))
register_adapter(Money, adapt_money)
def cast_currency_amount(v, cursor):
return None if v in (None, '(,)') else Money(*v[1:-1].split(','))
try:
oid = db.one("SELECT 'currency_amount'::regtype::oid")
register_type(new_type((oid,), 'currency_amount', cast_currency_amount))
except (psycopg2.ProgrammingError, NeedDatabase):
pass
def adapt_money_basket(b):
return AsIs(
"_wrap_amounts('%s'::jsonb)" %
json.dumps({k: str(v) for k, v in b.amounts.items() if v}).replace("'", "''")
)
register_adapter(MoneyBasket, adapt_money_basket)
def cast_currency_basket(v, cursor):
if v is None:
return None
parts = v[1:-1].split(',', 2)
if len(parts) == 2:
eur, usd = parts
obj = None
else:
eur, usd, obj = parts
if obj:
amounts = json.loads(obj[1:-1].replace('""', '"') if obj[0] == '"' else obj)
amounts = {k: Decimal(str(v)) for k, v in amounts.items()}
else:
amounts = {}
if eur:
amounts['EUR'] = Decimal(eur)
if usd:
amounts['USD'] = Decimal(usd)
return MoneyBasket(**amounts)
try:
oid = db.one("SELECT 'currency_basket'::regtype::oid")
register_type(new_type((oid,), 'currency_basket', cast_currency_basket))
except (psycopg2.ProgrammingError, NeedDatabase):
pass
use_qc = not env.override_query_cache
qc1 = QueryCache(db, threshold=(1 if use_qc else 0))
qc5 = QueryCache(db, threshold=(5 if use_qc else 0))
return {'db': db, 'db_qc1': qc1, 'db_qc5': qc5}
def database(env, tell_sentry):
dburl = env.database_url
maxconn = env.database_maxconn
try:
db = DB(dburl, maxconn=maxconn)
except psycopg2.OperationalError as e:
tell_sentry(e, {})
pg_dir = os.environ.get('OPENSHIFT_PG_DATA_DIR')
if pg_dir:
# We know where the postgres data is, try to start the server ourselves
r = call(['pg_ctl', '-D', pg_dir, 'start', '-w', '-t', '15'])
if r == 0:
return database(env, tell_sentry)
db = NoDB()
models = (
_AccountElsewhere,
AccountElsewhere,
_Community,
Community,
ExchangeRoute,
Participant,
Repository,
)
for model in models:
db.register_model(model)
liberapay.billing.payday.Payday.db = db
def adapt_money(m):
return AsIs('(%s,%s)::currency_amount' %
(adapt(m.amount), adapt(m.currency)))
register_adapter(Money, adapt_money)
def cast_currency_amount(v, cursor):
return None if v in (None, '(,)') else Money(*v[1:-1].split(','))
try:
oid = db.one("SELECT 'currency_amount'::regtype::oid")
register_type(
new_type((oid, ), _str('currency_amount'), cast_currency_amount))
except psycopg2.ProgrammingError:
pass
def adapt_money_basket(b):
return AsIs('(%s,%s)::currency_basket' %
(b.amounts['EUR'], b.amounts['USD']))
register_adapter(MoneyBasket, adapt_money_basket)
def cast_currency_basket(v, cursor):
if v is None:
return None
eur, usd = v[1:-1].split(',')
return MoneyBasket(EUR=Decimal(eur), USD=Decimal(usd))
try:
oid = db.one("SELECT 'currency_basket'::regtype::oid")
register_type(
new_type((oid, ), _str('currency_basket'), cast_currency_basket))
except psycopg2.ProgrammingError:
pass
use_qc = not env.override_query_cache
qc1 = QueryCache(db, threshold=(1 if use_qc else 0))
qc5 = QueryCache(db, threshold=(5 if use_qc else 0))
return {'db': db, 'db_qc1': qc1, 'db_qc5': qc5}
