Example #1
0
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)
Example #5
0
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
Example #6
0
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
Example #7
0
 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()
Example #8
0
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
Example #9
0
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
Example #10
0
 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)
Example #11
0
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)
Example #12
0
    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()
Example #13
0
    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
Example #14
0
 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)
Example #15
0
    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"})
Example #17
0
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
                           )
Example #18
0
    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()
Example #19
0
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))
Example #21
0
 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)
Example #22
0
    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"}])
Example #23
0
    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
Example #24
0
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']])
Example #25
0
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)
Example #26
0
    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")
Example #27
0
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)
Example #28
0
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
Example #29
0
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
Example #31
0
    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)
Example #34
0
    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()
Example #35
0
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)
Example #36
0
    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)
Example #37
0
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
                           )
Example #38
0
    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
Example #39
0
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)
Example #40
0
    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)))
Example #41
0
    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()
Example #42
0
    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))))
Example #43
0
    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.")
Example #44
0
    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))
Example #45
0
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
Example #46
0
    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)
Example #47
0
    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)
Example #48
0
    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)))
Example #49
0
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
Example #51
0
 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
Example #52
0
def get_connection():
    connection = connect(dbname='opentaps_seas')
    register_hstore(connection)
    return connection
Example #53
0
 def _create_conn(self):
     conn = psycopg2.connect(**self.postgresql_conn_info)
     register_hstore(conn)
     conn.set_session(autocommit=False)
     return conn
Example #54
0
 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)
Example #55
0
 def _enable_hstore(self):
     """Enables HSTORE support, if available."""
     try:
         register_hstore(self.db)
     except psycopg2.ProgrammingError:
         pass
Example #56
0
 def _connect(self, database, **kwargs):
     conn = super(PostgresqlExtDatabase, self)._connect(database, **kwargs)
     if self.register_hstore:
         register_hstore(conn, globally=True)
     return conn
Example #57
0
 def _connect(self):
     conn = super(PostgresqlExtDatabase, self)._connect()
     if self._register_hstore:
         register_hstore(conn, globally=True)
     return conn