def register_hstore_handler(connection, **kwargs): if not connection.settings_dict.get('HAS_HSTORE', True): return if sys.version_info[0] < 3: register_hstore(connection.connection, globally=True, unicode=True) else: register_hstore(connection.connection, globally=True)
def test_array_cast(self): from psycopg2.extras import register_hstore register_hstore(self.conn) cur = self.conn.cursor() cur.execute("select array['a=>1'::hstore, 'b=>2'::hstore];") a = cur.fetchone()[0] self.assertEqual(a, [{'a': '1'}, {'b': '2'}])
def test_roundtrip(self): from psycopg2.extras import register_hstore register_hstore(self.conn) cur = self.conn.cursor() def ok(d): cur.execute("select %s", (d,)) d1 = cur.fetchone()[0] self.assertEqual(len(d), len(d1)) for k in d: self.assert_(k in d1, k) self.assertEqual(d[k], d1[k]) ok({}) ok({"a": "b", "c": None}) ab = map(chr, range(32, 128)) ok(dict(zip(ab, ab))) ok({"".join(ab): "".join(ab)}) self.conn.set_client_encoding("latin1") if sys.version_info[0] < 3: ab = map(chr, range(32, 127) + range(160, 255)) else: ab = bytes(range(32, 127) + range(160, 255)).decode("latin1") ok({"".join(ab): "".join(ab)}) ok(dict(zip(ab, ab)))
def test_roundtrip_array(self): from psycopg2.extras import register_hstore register_hstore(self.conn) ds = [] ds.append({}) ds.append({"a": "b", "c": None}) ab = map(chr, range(32, 128)) ds.append(dict(zip(ab, ab))) ds.append({"".join(ab): "".join(ab)}) self.conn.set_client_encoding("latin1") if sys.version_info[0] < 3: ab = map(chr, range(32, 127) + range(160, 255)) else: ab = bytes(range(32, 127) + range(160, 255)).decode("latin1") ds.append({"".join(ab): "".join(ab)}) ds.append(dict(zip(ab, ab))) cur = self.conn.cursor() cur.execute("select %s", (ds,)) ds1 = cur.fetchone()[0] self.assertEqual(ds, ds1)
def register_type_handlers(connection, **kwargs): if connection.vendor != 'postgresql': return try: oids, array_oids = get_hstore_oids(connection.alias) register_hstore(connection.connection, globally=True, oid=oids, array_oid=array_oids) except ProgrammingError: # Hstore is not available on the database. # # If someone tries to create an hstore field it will error there. # This is necessary as someone may be using PSQL without extensions # installed but be using other features of contrib.postgres. # # This is also needed in order to create the connection in order to # install the hstore extension. pass try: citext_oids = get_citext_oids(connection.alias) array_type = psycopg2.extensions.new_array_type(citext_oids, 'citext[]', psycopg2.STRING) psycopg2.extensions.register_type(array_type, None) except ProgrammingError: # citext is not available on the database. # # The same comments in the except block of the above call to # register_hstore() also apply here. pass
def connect(dsn=None, *, timeout=TIMEOUT, loop=None, enable_json=True, enable_hstore=True, echo=False, **kwargs): """A factory for connecting to PostgreSQL. The coroutine accepts all parameters that psycopg2.connect() does plus optional keyword-only `loop` and `timeout` parameters. Returns instantiated Connection object. """ if loop is None: loop = asyncio.get_event_loop() waiter = asyncio.Future(loop=loop) conn = Connection(dsn, loop, timeout, waiter, bool(echo), **kwargs) try: yield from conn._poll(waiter, timeout) except Exception: conn.close() raise if enable_json: extras.register_default_json(conn._conn) if enable_hstore: oids = yield from _enable_hstore(conn) if oids is not None: oid, array_oid = oids extras.register_hstore(conn._conn, oid=oid, array_oid=array_oid) return conn
def __init__(self, engine, **kw): super(HStore, self).__init__(engine, **kw) spliturl = urlsplit(engine) try: self._conn = conn = psycopg2.connect( host=spliturl.hostname, port=spliturl.port, database=spliturl.path, user=spliturl.username or "", password=spliturl.password or "", ) self._store = db = conn.cursor(cursor_factory=extras.RealDictCursor) except psycopg2.OperationalError: logging.exception("configuration error") raise TypeError("configuration error") try: db.execute("CREATE EXTENSION hstore") conn.commit() except psycopg2.ProgrammingError: conn.rollback() extras.register_hstore(conn) try: db.execute("CREATE TABLE shove (id serial PRIMARY KEY, data hstore)") conn.commit() db.execute("INSERT INTO shove (data) VALUES (%s)", ['"key"=>"value"']) conn.commit() db.execute("UPDATE shove SET data = delete(data, %s)", ["key"]) except psycopg2.ProgrammingError: conn.rollback()
def correct_qa_config(cursor, branch_id, merge_base_commit_hash): """ Return the config_id to release a new commit to this build with """ register_hstore(cursor) # if there are releases on this branch, use the config from the most recent # otherwise use the most recent release of the merge base commit cursor.execute( ("select config_id " " from release " " join iteration using (iteration_id) " " where branch_id=%s " " or commit_hash=%s " " order by iteration.created_dt desc, release.created_dt desc " " limit 1 "), (branch_id, merge_base_commit_hash), ) results = cursor.fetchall() if len(results) is 0: config_id = save( cursor, 'config', # table ['key_value_pairs'], # unique columns ['key_value_pairs'], # column ({},), # value ) elif len(results) is 1: config_id = results[0][0] else: raise ValueError("There should only be one correct config") return config_id
def register_type_handlers(connection, **kwargs): if connection.vendor != 'postgresql': return try: register_hstore(connection.connection, globally=True) except ProgrammingError: # Hstore is not available on the database. # # If someone tries to create an hstore field it will error there. # This is necessary as someone may be using PSQL without extensions # installed but be using other features of contrib.postgres. # # This is also needed in order to create the connection in order to # install the hstore extension. pass try: with connection.cursor() as cursor: # Retrieve oids of citext arrays. cursor.execute("SELECT typarray FROM pg_type WHERE typname = 'citext'") oids = tuple(row[0] for row in cursor) array_type = psycopg2.extensions.new_array_type(oids, 'citext[]', psycopg2.STRING) psycopg2.extensions.register_type(array_type, None) except ProgrammingError: # citext is not available on the database. # # The same comments in the except block of the above call to # register_hstore() also apply here. pass
def on_connect(conn): hstore_oids = self._hstore_oids(conn) if hstore_oids is not None: oid, array_oid = hstore_oids if util.py2k: extras.register_hstore(conn, oid=oid, array_oid=array_oid, unicode=True) else: extras.register_hstore(conn, oid=oid, array_oid=array_oid)
def setup_postgresql_hstore_extension(sender, connection, **kwargs): from psycopg2.extras import register_hstore cursor = connection.connection.cursor() cursor.execute('CREATE EXTENSION IF NOT EXISTS hstore') if PY2: register_hstore(connection.connection, globally=True, unicode=True) else: register_hstore(connection.connection, globally=True)
def renderTile(self, width, height, srs, coord): """ Render a single tile, return a SaveableResponse instance. """ # tilesize = self.tileSize * 16 # # # center pixel on zoomlevel # center = (tilesize << coord.zoom) >> 1 # # maximum coordinate in web mercator # f900913 = 20037508.342789244 # # # pixel relative to global center # dx = (coord.column * tilesize) - center # # flip y-axis # dy = center - (coord.row * tilesize) # # # size of one pixel # div = f900913 / center # conn = _connect(self.dbdsn) db = conn.cursor() register_hstore(conn, True, False) db.execute(self.query_tile, (coord.column * self.tileSize, coord.row * self.tileSize, coord.zoom)) rows = db.fetchall() logging.debug(self.query_tile) bbox = 0 tile = {"bbox": bbox, "granularity":10000, "features":[]} features = [] for row in rows: # empty geometry if (row[0] is None) or (row[1] is None): continue #logging.debug(str(row[1])) geojson = json.loads(str(row[1])) # tags = {} # for tag in row[0].iteritems(): # tags[tag[0]] = ("%s" %(tag[1])).decode('utf-8') # # print tags # # geojson["properties"] = tags geojson["properties"] = row[0] features.append(geojson) tile["features"].extend(features) try: conn.commit() except Exception, e: logging.error(">>> %s", e) conn.rollback()
def _register_hstore_converter(self, engine): from psycopg2.extras import register_hstore from psycopg2 import ProgrammingError connection = engine.connect() try: register_hstore(connection.connection, globally=True) except ProgrammingError: pass
def on_connect(conn): hstore_oids = self._hstore_oids(conn) if hstore_oids is not None: oid, array_oid = hstore_oids kw = {'oid': oid} if util.py2k: kw['unicode'] = True if self.psycopg2_version >= (2, 4, 3): kw['array_oid'] = array_oid extras.register_hstore(conn, **kw)
def _cursor(self): # ensure that we're connected cursor = super(DatabaseWrapper, self)._cursor() # register hstore extension register_hstore(self.connection, globally=True, unicode=True) # bypass future registrations self._cursor = super(DatabaseWrapper, self)._cursor return cursor
def test_register_curs(self): from psycopg2.extras import register_hstore cur = self.conn.cursor() register_hstore(cur) cur.execute("select null::hstore, ''::hstore, 'a => b'::hstore") t = cur.fetchone() self.assert_(t[0] is None) self.assertEqual(t[1], {}) self.assertEqual(t[2], {"a": "b"})
def get_tasks_from_db(args): db_user = args.user if not args.user: db_user = getpass.getuser() db_name = args.database if not args.database: db_name = 'osm' db_schema = args.schema if not args.schema: db_schema = 'osmosis' db_pass = args.password if not args.password: db_pass = getpass.getpass('please enter the password for database {dbname} and user {dbuser}: '.format(dbname=db_name, dbuser=db_user)) db_query = args.query db_string = "dbname={db_name} user={db_user} password={db_pass}".format(db_name=db_name, db_user=db_user, db_pass=db_pass, ) if args.host: db_string += " host={db_host}".format(db_host=args.host) # open a connection, get a cursor conn = psycopg2.connect(db_string) cur = conn.cursor(cursor_factory=DictCursor) if db_schema == 'osmosis': # hstore is only default in osmosis schema register_hstore(cur) # get our results cur.execute(db_query) nodes = cur.fetchall() for node in nodes: osmid = node.get("id") geom = { "type": "FeatureCollection", "features": [{ "type": "Feature", "properties": {"osmid": osmid}, "geometry": json.loads(geojson.dumps( wkb.loads(node["geom"].decode("hex")))) }] } yield prepare_task(node=node, args=args, osmid=osmid, geom=geom )
def renderTile(self, width, height, srs, coord): """ Render a single tile, return a SaveableResponse instance. """ tilesize = 256 * 16 # center pixel on zoomlevel center = (tilesize << coord.zoom) >> 1 # maximum coordinate in web mercator f900913 = 20037508.342789244 # pixel relative to global center dx = (coord.column * tilesize) - center # flip y-axis dy = center - (coord.row * tilesize) # size of one pixel div = f900913 / center geomparser = WKBParser(dx, dy, div) conn = _connect(self.dbdsn) db = conn.cursor() register_hstore(conn, True, False) db.execute(self.query_tile, (coord.column, coord.row, coord.zoom)) rows = db.fetchall() tile = TileData_pb2.Data() tagdict = {} for row in rows: # empty geometry if (row[0] is None) or (row[1] is None): continue self.addItem(tile, row, coord, geomparser, tagdict) db.execute(self.query_tile_poi, (coord.column, coord.row, coord.zoom)) rows = db.fetchall() for row in rows: # empty geometry if (row[0] is None) or (row[1] is None): continue self.addItem(tile, row, coord, geomparser, tagdict) #db.close() tile.num_tags = len(tile.keys) try: conn.commit() except Exception, e: logging.error(">>> %s", e) conn.rollback()
def register_hstore_on_connection_creation(connection, sender, *args, **kwargs): dbname = connection.alias if dbname not in _oids: oids1, oids2 = HstoreAdapter.get_oids(connection.connection) if not oids1 and not oids2: raise DatabaseError("hstore isn't installed on this database") _oids[dbname] = (oids1[0], oids2[0]) oid, array_oid = _oids[dbname] register_hstore(connection.connection, globally=True, oid=oid, array_oid=array_oid)
def test_register_unicode(self): from psycopg2.extras import register_hstore register_hstore(self.conn, unicode=True) cur = self.conn.cursor() cur.execute("select null::hstore, ''::hstore, 'a => b'::hstore") t = cur.fetchone() self.assert_(t[0] is None) self.assertEqual(t[1], {}) self.assertEqual(t[2], {u"a": u"b"}) self.assert_(isinstance(t[2].keys()[0], unicode)) self.assert_(isinstance(t[2].values()[0], unicode))
def on_connect(conn): hstore_oids = self._hstore_oids(conn) if hstore_oids is not None: oid, array_oid = hstore_oids kw = {"oid": oid} if util.py2k: kw["unicode"] = True if ( self.psycopg2_version >= self.FEATURE_VERSION_MAP["array_oid"] ): kw["array_oid"] = array_oid extras.register_hstore(conn, **kw)
def test_array_cast_oid(self): cur = self.conn.cursor() cur.execute("select 'hstore'::regtype::oid, 'hstore[]'::regtype::oid") oid, aoid = cur.fetchone() register_hstore(None, globally=True, oid=oid, array_oid=aoid) cur.execute(""" select null::hstore, ''::hstore, 'a => b'::hstore, '{a=>b}'::hstore[]""") t = cur.fetchone() self.assert_(t[0] is None) self.assertEqual(t[1], {}) self.assertEqual(t[2], {"a": "b"}) self.assertEqual(t[3], [{"a": "b"}])
def _make_conn(self, conn_info): # if multiple hosts are provided, select one at random as a kind of # simple load balancing. host = conn_info.get('host') if host and isinstance(host, list): host = random.choice(host) conn_info = conn_info.copy() conn_info['host'] = host conn = psycopg2.connect(**conn_info) conn.set_session(readonly=self.readonly, autocommit=True) register_hstore(conn) register_json(conn, loads=ujson.loads) return conn
def includeme(config): settings = config.registry.settings # init Postgres engine = DBSession.bind.engine conn = DBSession.connection() dialect = conn.dialect.name.lower() if dialect == 'postgresql': from psycopg2.extras import register_hstore initializedb.add_extension(engine, 'hstore') register_hstore(engine.raw_connection(), True) # initializedb if settings.get('sacrud.debug_reload_database', False): initializedb.main(argv=["init", settings['ini_file']])
def register_hstore_handler(connection, **kwargs): # do not register hstore if DB is not postgres # do not register if HAS_HSTORE flag is set to false if connection.vendor != "postgresql" or connection.settings_dict.get("HAS_HSTORE", True) is False: return # if the ``NAME`` of the database in the connection settings is ``None`` # defer hstore registration by setting up a new unique handler if connection.settings_dict["NAME"] is None: connection_handler.attach_handler(register_hstore_handler, vendor="postgresql", unique=HSTORE_REGISTER_GLOBALLY) return if sys.version_info[0] < 3: register_hstore(connection.connection, globally=HSTORE_REGISTER_GLOBALLY, unicode=True) else: register_hstore(connection.connection, globally=HSTORE_REGISTER_GLOBALLY)
def renderTile(self, width, height, srs, coord): """ Render a single tile, return a SaveableResponse instance. """ ##return EmptyResponse(None, coord) tile = VectorTile(self.extents) conn = _connect(self.dbdsn) db = conn.cursor() register_hstore(conn, True, False) try: db.execute(self.query_tile, (coord.column, coord.row, coord.zoom)) except Exception, e: logging.error("db: %s\n %s", coord, e) raise KnownUnknown("query failed")
def lookup_trace(trace_id): cur = conn.cursor(cursor_factory=pgextras.DictCursor) pgextras.register_hstore(cur) cur.execute( "SELECT * FROM trv_trace WHERE traceroute_id = {id} ORDER BY traceroute_id,hop_number;" .format(id=trace_id)) rows = cur.fetchall() if "table" in request.args: headers = [ "reporter", "traceroute_id", "origin_ip", "dest_ip", "probe_id", "hop_number", "host", "hop_kvs" ] return tabulate(rows, headers, tablefmt="psql") else: out = [json.dumps(x) for x in rows] return "\n".join(out)
def register_hstore_handler(connection, **kwargs): if connection.vendor != 'postgresql': return try: register_hstore(connection.connection, globally=True) except ProgrammingError: # Hstore is not available on the database. # # If someone tries to create an hstore field it will error there. # This is necessary as someone may be using PSQL without extensions # installed but be using other features of contrib.postgres. # # This is also needed in order to create the connection in order to # install the hstore extension. pass
def connect_to_db(): print("=================================================================") print("Trying to create a connection") try: connection = psycopg2.connect( user="******", password="", # Replace with your password while using host="127.0.0.1", port="5432", database="nominatim") register_hstore(connection, globally=True, unicode=True) print("Success") return connection except: print("Failed") exit
def register_type_handlers(connection, **kwargs): if connection.vendor != 'postgresql' or connection.alias == NO_DB_ALIAS: return try: oids, array_oids = get_hstore_oids(connection.alias) register_hstore(connection.connection, globally=False, oid=oids, array_oid=array_oids) except ProgrammingError: # Hstore is not available on the database. # # If someone tries to create an hstore field it will error there. # This is necessary as someone may be using PSQL without extensions # installed but be using other features of contrib.postgres. # # This is also needed in order to create the connection in order to # install the hstore extension. pass
def test_oid(self): cur = self.conn.cursor() cur.execute("select 'hstore'::regtype::oid") oid = cur.fetchone()[0] # Note: None as conn_or_cursor is just for testing: not public # interface and it may break in future. from psycopg2.extras import register_hstore register_hstore(None, globally=True, oid=oid) try: cur.execute("select null::hstore, ''::hstore, 'a => b'::hstore") t = cur.fetchone() self.assertTrue(t[0] is None) self.assertEqual(t[1], {}) self.assertEqual(t[2], {'a': 'b'}) finally: psycopg2.extensions.string_types.pop(oid)
def test_oid(self): cur = self.conn.cursor() cur.execute("select 'hstore'::regtype::oid") oid = cur.fetchone()[0] # Note: None as conn_or_cursor is just for testing: not public # interface and it may break in future. from psycopg2.extras import register_hstore register_hstore(None, globally=True, oid=oid) try: cur.execute("select null::hstore, ''::hstore, 'a => b'::hstore") t = cur.fetchone() self.assert_(t[0] is None) self.assertEqual(t[1], {}) self.assertEqual(t[2], {'a': 'b'}) finally: psycopg2.extensions.string_types.pop(oid)
def test_array_cast_oid(self): cur = self.conn.cursor() cur.execute("select 'hstore'::regtype::oid, 'hstore[]'::regtype::oid") oid, aoid = cur.fetchone() from psycopg2.extras import register_hstore register_hstore(None, globally=True, oid=oid, array_oid=aoid) try: cur.execute("select null::hstore, ''::hstore, 'a => b'::hstore, '{a=>b}'::hstore[]") t = cur.fetchone() self.assert_(t[0] is None) self.assertEqual(t[1], {}) self.assertEqual(t[2], {'a': 'b'}) self.assertEqual(t[3], [{'a': 'b'}]) finally: psycopg2.extensions.string_types.pop(oid) psycopg2.extensions.string_types.pop(aoid)
def test_non_dbapi_connection(self): conn = self.connect(connection_factory=RealDictConnection) try: register_hstore(conn) curs = conn.cursor() curs.execute("select ''::hstore as x") self.assertEqual(curs.fetchone()["x"], {}) finally: conn.close() conn = self.connect(connection_factory=RealDictConnection) try: curs = conn.cursor() register_hstore(curs) curs.execute("select ''::hstore as x") self.assertEqual(curs.fetchone()["x"], {}) finally: conn.close()
def register_hstore_handler(connection, **kwargs): # do not register hstore if DB is not postgres # do not register if HAS_HSTORE flag is set to false if connection.vendor != 'postgresql' or \ connection.settings_dict.get('HAS_HSTORE', True) is False: return # if the ``NAME`` of the database in the connection settings is ``None`` # defer hstore registration by setting up a new unique handler if connection.settings_dict['NAME'] is None: connection_handler.attach_handler(register_hstore_handler, vendor="postgresql", unique=HSTORE_REGISTER_GLOBALLY) return if sys.version_info[0] < 3: register_hstore(connection.connection, globally=HSTORE_REGISTER_GLOBALLY, unicode=True) else: register_hstore(connection.connection, globally=HSTORE_REGISTER_GLOBALLY)
def get_tasks_from_db(args): db_user = args.user if not args.user: db_user = getpass.getuser() db_name = args.database if not args.database: db_name = 'osm' db_query = args.query db_string = "dbname={db_name} user={db_user}".format(db_name=db_name, db_user=db_user ) if args.host: db_string += " host={db_host}".format(db_host=args.host) # open a connection, get a cursor conn = psycopg2.connect(db_string) cur = conn.cursor(cursor_factory=DictCursor) register_hstore(cur) # get our results cur.execute(db_query) nodes = cur.fetchall() for node in nodes: osmid = node["id"] geom = { "type": "FeatureCollection", "features": [{ "type": "Feature", "properties": {"osmid": osmid}, "geometry": json.loads(geojson.dumps( wkb.loads(node["geom"].decode("hex")))) }] } yield prepare_task(node=node, args=args, osmid=osmid, geom=geom )
async def _connect(self): try: await self._poll(self._waiter, self._timeout) except Exception: self.close() raise if self._enable_json: extras.register_default_json(self._conn) if self._enable_uuid: extras.register_uuid(conn_or_curs=self._conn) if self._enable_hstore: oids = await self._get_oids() if oids is not None: oid, array_oid = oids extras.register_hstore(self._conn, oid=oid, array_oid=array_oid) return self
def register_hstore_on_connection_creation(connection, sender, *args, **kwargs): oid = HstoreAdapter.get_oids(connection.connection) if oid is None or not oid[0]: if connection.connection.server_version < 90000: raise psycopg2.ProgrammingError("Database version not supported") elif connection.connection.server_version < 90100: pg_config = subprocess.Popen(["pg_config", "--sharedir"], stdout=subprocess.PIPE) share_dir = pg_config.communicate()[0].strip('\r\n ') hstore_sql = os.path.join(share_dir, 'contrib', 'hstore.sql') statements = re.compile(r";[ \t]*$", re.M) cursor = connection.cursor() with open(hstore_sql, 'U') as fp: for statement in statements.split(fp.read().decode(settings.FILE_CHARSET)): statement = re.sub(ur"--.*([\n\Z]|$)", "", statement).strip() if statement: cursor.execute(statement + u";") else: cursor = connection.cursor() cursor.execute("CREATE EXTENSION hstore;") register_hstore(connection.connection, globally=True)
def test_roundtrip_unicode(self): register_hstore(self.conn, unicode=True) cur = self.conn.cursor() def ok(d): cur.execute("select %s", (d, )) d1 = cur.fetchone()[0] self.assertEqual(len(d), len(d1)) for k, v in d1.iteritems(): self.assert_(k in d, k) self.assertEqual(d[k], v) self.assert_(isinstance(k, unicode)) self.assert_(v is None or isinstance(v, unicode)) ok({}) ok({"a": "b", "c": None, "d": u"\u20ac", u"\u2603": "e"}) ab = map(unichr, range(1, 1024)) ok({u"".join(ab): u"".join(ab)}) ok(dict(zip(ab, ab)))
def test_non_dbapi_connection(self): from psycopg2.extras import RealDictConnection from psycopg2.extras import register_hstore conn = psycopg2.connect(dsn, connection_factory=RealDictConnection) try: register_hstore(conn) curs = conn.cursor() curs.execute("select ''::hstore as x") self.assertEqual(curs.fetchone()['x'], {}) finally: conn.close() conn = psycopg2.connect(dsn, connection_factory=RealDictConnection) try: curs = conn.cursor() register_hstore(curs) curs.execute("select ''::hstore as x") self.assertEqual(curs.fetchone()['x'], {}) finally: conn.close()
def test_roundtrip_unicode(self): from psycopg2.extras import register_hstore register_hstore(self.conn, str=True) cur = self.conn.cursor() def ok(d): cur.execute("select %s", (d, )) d1 = cur.fetchone()[0] self.assertEqual(len(d), len(d1)) for k, v in d1.items(): self.assertTrue(k in d, k) self.assertEqual(d[k], v) self.assertTrue(isinstance(k, str)) self.assertTrue(v is None or isinstance(v, str)) ok({}) ok({'a': 'b', 'c': None, 'd': '\u20ac', '\u2603': 'e'}) ab = list(map(chr, list(range(1, 1024)))) ok({''.join(ab): ''.join(ab)}) ok(dict(list(zip(ab, ab))))
def migrate(cls): """migrate data from mongodb""" mongo = get_mongodb() with pg_conn_context() as pg_conn, pg_conn.cursor() as cursor: register_uuid(conn_or_curs=cursor) register_hstore(conn_or_curs=cursor) results = mongo.get_collection("verification_requests").find() for each in results: insert_query = """ INSERT INTO identity_verify_requests (request_id, identifier, method, status, create_time, extra) VALUES (%s,%s,%s,%s,%s,%s) """ cursor.execute(insert_query, (each['request_id'], each['sid_orig'], each['verification_method'], each['status'], each['create_time'], { 'password': each['password'] } if 'password' in each else None)) pg_conn.commit() print("Migration finished.")
def test_register_globally(self): from psycopg2.extras import register_hstore, HstoreAdapter oids = HstoreAdapter.get_oids(self.conn) try: register_hstore(self.conn, globally=True) conn2 = psycopg2.connect(dsn) try: cur2 = self.conn.cursor() cur2.execute("select 'a => b'::hstore") r = cur2.fetchone() self.assertTrue(isinstance(r[0], dict)) finally: conn2.close() finally: psycopg2.extensions.string_types.pop(oids[0][0]) # verify the caster is not around anymore cur = self.conn.cursor() cur.execute("select 'a => b'::hstore") r = cur.fetchone() self.assertTrue(isinstance(r[0], str))
def _connect(dsn=None, *, timeout=TIMEOUT, loop=None, enable_json=True, enable_hstore=True, enable_uuid=True, echo=False, **kwargs): if loop is None: loop = asyncio.get_event_loop() waiter = create_future(loop) conn = Connection(dsn, loop, timeout, waiter, bool(echo), **kwargs) try: yield from conn._poll(waiter, timeout) except Exception: conn.close() raise if enable_json: extras.register_default_json(conn._conn) if enable_uuid: extras.register_uuid(conn_or_curs=conn._conn) if enable_hstore: oids = yield from _enable_hstore(conn) if oids is not None: oid, array_oid = oids extras.register_hstore(conn._conn, oid=oid, array_oid=array_oid) return conn
def new_register_request(cls, sid_orig: str, verification_method: str, status: str, password: str = None) -> str: """ 新增一条注册请求 :param sid_orig: original sid :param verification_method: password or email :param status: status of the request :param password: if register by password, fill everyclass password here :return: the `request_id` """ if verification_method not in ("email", "password"): raise ValueError( "verification_method must be one of email, password") request_id = uuid.uuid4() with pg_conn_context() as conn, conn.cursor() as cursor: register_hstore(conn_or_curs=cursor) extra_doc = {} if password: extra_doc.update( {"password": generate_password_hash(password)}) insert_query = """ INSERT INTO identity_verify_requests (request_id, identifier, method, status, create_time, extra) VALUES (%s,%s,%s,%s,%s,%s) """ cursor.execute(insert_query, (request_id, sid_orig, verification_method, status, datetime.datetime.now(), extra_doc)) conn.commit() return str(request_id)
def test_roundtrip_array(self): register_hstore(self.conn) ds = [{}, {"a": "b", "c": None}] ab = list(map(chr, range(32, 128))) ds.append(dict(zip(ab, ab))) ds.append({"".join(ab): "".join(ab)}) self.conn.set_client_encoding("latin1") if PY2: ab = map(chr, range(32, 127) + range(160, 255)) else: ab = bytes(list(range(32, 127)) + list(range(160, 255))).decode("latin1") ds.append({"".join(ab): "".join(ab)}) ds.append(dict(zip(ab, ab))) cur = self.conn.cursor() cur.execute("select %s", (ds, )) ds1 = cur.fetchone()[0] self.assertEqual(ds, ds1)
def test_roundtrip(self): from psycopg2.extras import register_hstore register_hstore(self.conn) cur = self.conn.cursor() def ok(d): cur.execute("select %s", (d,)) d1 = cur.fetchone()[0] self.assertEqual(len(d), len(d1)) for k in d: self.assert_(k in d1, k) self.assertEqual(d[k], d1[k]) ok({}) ok({'a': 'b', 'c': None}) ab = map(chr, range(32, 128)) ok(dict(zip(ab, ab))) ok({''.join(ab): ''.join(ab)}) self.conn.set_client_encoding('latin1') ab = map(chr, range(1, 256)) ok({''.join(ab): ''.join(ab)}) ok(dict(zip(ab, ab)))
def register_hstore_on_connection_creation(connection, sender, *args, **kwargs): oid = HstoreAdapter.get_oids(connection.connection) if oid is None or not oid[0]: if connection.connection.server_version < 90000: raise psycopg2.ProgrammingError("Database version not supported") elif connection.connection.server_version < 90100: pg_config = subprocess.Popen(["pg_config", "--sharedir"], stdout=subprocess.PIPE) share_dir = pg_config.communicate()[0].strip('\r\n ') hstore_sql = os.path.join(share_dir, 'contrib', 'hstore.sql') statements = re.compile(r";[ \t]*$", re.M) cursor = connection.cursor() with open(hstore_sql, 'U') as fp: for statement in statements.split(fp.read().decode( settings.FILE_CHARSET)): statement = re.sub(ur"--.*([\n\Z]|$)", "", statement).strip() if statement: cursor.execute(statement + u";") else: cursor = connection.cursor() cursor.execute("CREATE EXTENSION hstore;") register_hstore(connection.connection, globally=True)
def get_pgconnection(): pgconnection = pgconnect(dbname='opentaps_seas') register_hstore(pgconnection) return pgconnection
def connect(self, database, **kwargs): conn = super(PostgresqlExtAdapter, self).connect(database, **kwargs) register_hstore(conn, globally=True) self._ltree_support = register_ltree(conn) return conn
def get_connection(): connection = connect(dbname='opentaps_seas') register_hstore(connection) return connection
def _create_conn(self): conn = psycopg2.connect(**self.postgresql_conn_info) register_hstore(conn) conn.set_session(autocommit=False) return conn
def _create_test_db(self, verbosity, autoclobber): super(DatabaseCreation, self)._create_test_db(verbosity, autoclobber) self.install_hstore_contrib() register_hstore(self.connection.connection, globally=True, unicode=True)
def _enable_hstore(self): """Enables HSTORE support, if available.""" try: register_hstore(self.db) except psycopg2.ProgrammingError: pass
def _connect(self, database, **kwargs): conn = super(PostgresqlExtDatabase, self)._connect(database, **kwargs) if self.register_hstore: register_hstore(conn, globally=True) return conn
def _connect(self): conn = super(PostgresqlExtDatabase, self)._connect() if self._register_hstore: register_hstore(conn, globally=True) return conn