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}