Python psycopg2.extras方法代码示例

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def register_json_typecasters(conn, loads_fn):
    """Set the function for converting JSON data for a connection.

    Use the supplied function to decode JSON data returned from the database
    via the given connection. The function should accept a single argument of
    the data as a string encoded in the database's character encoding.
    psycopg2's default handler for JSON data is json.loads.
    http://initd.org/psycopg/docs/extras.html#json-adaptation

    This function attempts to register the typecaster for both JSON and JSONB
    types.

    Returns a set that is a subset of {'json', 'jsonb'} indicating which types
    (if any) were successfully registered.
    """
    available = set()

    for name in ["json", "jsonb"]:
        try:
            psycopg2.extras.register_json(conn, loads=loads_fn, name=name)
            available.add(name)
        except psycopg2.ProgrammingError:
            pass

    return available 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
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 t.oid FROM pg_type t WHERE t.typname = 'hstore' and t.typisdefined"
            )
            oid = cur.fetchone()[0]
            ext.register_type(ext.new_type((oid,), "HSTORE", ext.UNICODE))
        except Exception:
            pass 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def load_csv(self, file, count):
        file.seek(0)
        stream_schema_message = self.stream_schema_message
        stream = stream_schema_message['stream']
        logger.info("Loading {} rows into '{}'".format(count, stream))

        with self.open_connection() as connection:
            with connection.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
                cur.execute(self.create_table_query(True))
                copy_sql = "COPY {} ({}) FROM STDIN WITH (FORMAT CSV, ESCAPE '\\')".format(
                    self.table_name(stream, True),
                    ', '.join(self.column_names())
                )
                logger.info(copy_sql)
                cur.copy_expert(
                    copy_sql,
                    file
                )
                if len(self.stream_schema_message['key_properties']) > 0:
                    cur.execute(self.update_from_temp_table())
                    logger.info(cur.statusmessage)
                cur.execute(self.insert_from_temp_table())
                logger.info(cur.statusmessage)
                cur.execute(self.drop_temp_table()) 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def __init__(self):

        self.log = open('.database_actions.log', 'a')
        
        self.database_name  = "odds_data"
        self.matches_table  = "matches"
        
        self.odds_cols      = ['1', 'X', '2']
        self.timestamp_col  = "timestamp"
        
        self.teams          = teams()

        try:
            self.connect()

        except psycopg2.OperationalError:
            self.create_database()
            self.connect()

        self.cursor = self.connection.cursor(cursor_factory = psycopg2.extras.DictCursor)
        self.create_table() 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def lambda_handler(event, context):
    with psycopg2.connect(CONNECTION) as conn:
        with conn.cursor() as cursor:
            response = requests.get(URL)
            feed = gtfs.FeedMessage()
            feed.ParseFromString(response.content)

            # performant way to batch inserts
            # see http://initd.org/psycopg/docs/extras.html#psycopg2.extras.execute_batch
            start = time.time()
            execute_values(
                cursor,
                "INSERT INTO mta (vid, time, route_id, bearing, geom)"
                "VALUES %s", parse_vehicles(feed))
            conn.commit()
            end = time.time()
            nrows = len(feed.entity)
            
    return {
        'statusCode': 200,
        'body': f"INSERTED {nrows} rows at {end}, (elapsed: {end - start})"
    } 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def executemany(cur, stmt):
    """Runs executemany on the value set with the provided cursor.

    This function is a wrapper around the Python Database API 'executemany'
    to accommodate for psycopg2 slow 'executemany' implementation.

    Parameters
    ----------
    cur : cursor
        The cursor to run the statement with
    stmt : str
        The SQL statement to execute on
    """
    if cur is not None:
        if cfg.db_type != DBType.POSTGRES.value:
            cur.executemany(stmt, cfg.input_data)
        else:
            import psycopg2.extras as p
            p.execute_batch(cur, stmt, cfg.input_data) 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def test_jsonify(self):
        freaky_things = (
            (
                'model',
                models.construct_node(
                    title={'English': 'a'},
                    type_constraint='integer',
                )
            ),
            ('bytes', b'a'),
            ('datetime', datetime.datetime.now()),
            ('decimal', Decimal('2.3')),
            ('range', psycopg2.extras.Range()),
            ('not actually', 'freaky'),
        )
        for k, v in freaky_things[:-1]:
            self.assertRaises(TypeError, json.dumps, {k: v})

        self.assertIsNotNone(
            json.dumps({k: models.jsonify(v) for k, v in freaky_things})
        ) 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def init_db(self, force=False):
        """DB init entrypoint."""
        cur = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

        cur.execute("CREATE TABLE IF NOT EXISTS public.versions (deployment text, version integer)")

        cur.execute('SET search_path TO {},public'.format(get_conf().deployment_name))

        if force:
            cur.execute("DELETE FROM public.versions WHERE deployment = %s", (get_conf().deployment_name,))
            cur.execute('DROP SCHEMA IF EXISTS {} CASCADE'.format(get_conf().deployment_name))

        if not self._check_schema_version(cur, get_conf().deployment_name):
            self._create_tables()

        self.commit()
        cur.close() 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def test_cursor_factory(self):
        self.assertEqual(self.conn.cursor_factory, None)
        cur = self.conn.cursor()
        cur.execute("select 1 as a")
        self.assertRaises(TypeError, (lambda r: r['a']), cur.fetchone())

        self.conn.cursor_factory = psycopg2.extras.DictCursor
        self.assertEqual(self.conn.cursor_factory, psycopg2.extras.DictCursor)
        cur = self.conn.cursor()
        cur.execute("select 1 as a")
        self.assertEqual(cur.fetchone()['a'], 1)

        self.conn.cursor_factory = None
        self.assertEqual(self.conn.cursor_factory, None)
        cur = self.conn.cursor()
        cur.execute("select 1 as a")
        self.assertRaises(TypeError, (lambda r: r['a']), cur.fetchone()) 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def testUUIDARRAY(self):
        import uuid
        psycopg2.extras.register_uuid()
        u = [uuid.UUID('9c6d5a77-7256-457e-9461-347b4358e350'), uuid.UUID('9c6d5a77-7256-457e-9461-347b4358e352')]
        s = self.execute("SELECT %s AS foo", (u,))
        self.failUnless(u == s)
        # array with a NULL element
        u = [uuid.UUID('9c6d5a77-7256-457e-9461-347b4358e350'), None]
        s = self.execute("SELECT %s AS foo", (u,))
        self.failUnless(u == s)
        # must survive NULL cast to a uuid[]
        s = self.execute("SELECT NULL::uuid[] AS foo")
        self.failUnless(s is None)
        # what about empty arrays?
        s = self.execute("SELECT '{}'::uuid[] AS foo")
        self.failUnless(type(s) == list and len(s) == 0) 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def test_inet_conform(self):
        from psycopg2.extras import Inet
        i = Inet("192.168.1.0/24")
        a = psycopg2.extensions.adapt(i)
        a.prepare(self.conn)
        self.assertEqual(
            filter_scs(self.conn, b("E'192.168.1.0/24'::inet")),
            a.getquoted())

        # adapts ok with unicode too
        i = Inet(u"192.168.1.0/24")
        a = psycopg2.extensions.adapt(i)
        a.prepare(self.conn)
        self.assertEqual(
            filter_scs(self.conn, b("E'192.168.1.0/24'::inet")),
            a.getquoted()) 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def test_adapt_8(self):
        if self.conn.server_version >= 90000:
            return self.skipTest("skipping dict adaptation with PG pre-9 syntax")

        from psycopg2.extras import HstoreAdapter

        o = {'a': '1', 'b': "'", 'c': None}
        if self.conn.encoding == 'UTF8':
            o['d'] = u'\xe0'

        a = HstoreAdapter(o)
        a.prepare(self.conn)
        q = a.getquoted()

        self.assert_(q.startswith(b("((")), q)
        ii = q[1:-1].split(b("||"))
        ii.sort()

        self.assertEqual(len(ii), len(o))
        self.assertEqual(ii[0], filter_scs(self.conn, b("(E'a' => E'1')")))
        self.assertEqual(ii[1], filter_scs(self.conn, b("(E'b' => E'''')")))
        self.assertEqual(ii[2], filter_scs(self.conn, b("(E'c' => NULL)")))
        if 'd' in o:
            encc = u'\xe0'.encode(psycopg2.extensions.encodings[self.conn.encoding])
            self.assertEqual(ii[3], filter_scs(self.conn, b("(E'd' => E'") + encc + b("')"))) 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def _wait_select(conn):
    """
        copy-pasted from psycopg2.extras.wait_select
        the default implementation doesn't define a timeout in the select calls
    """
    while 1:
        try:
            state = conn.poll()
            if state == POLL_OK:
                break
            elif state == POLL_READ:
                select.select([conn.fileno()], [], [], _WAIT_SELECT_TIMEOUT)
            elif state == POLL_WRITE:
                select.select([], [conn.fileno()], [], _WAIT_SELECT_TIMEOUT)
            else:
                raise conn.OperationalError("bad state from poll: %s" % state)
        except KeyboardInterrupt:
            conn.cancel()
            # the loop will be broken by a server error
            continue
        except select.error as e:
            errno = e.args[0]
            if errno != 4:
                raise


# When running a query, make pressing CTRL+C raise a KeyboardInterrupt
# See http://initd.org/psycopg/articles/2014/07/20/cancelling-postgresql-statements-python/
# See also https://github.com/psycopg/psycopg2/issues/468 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def query(self, query, params=None):
        with self.open_connection() as connection:
            with connection.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
                cur.execute(
                    query,
                    params
                )

                if cur.rowcount > 0:
                    return cur.fetchall()
                else:
                    return [] 

# 需要导入模块: import psycopg2 [as 别名]
# 或者: from psycopg2 import extras [as 别名]
def copy_from(self, file, table):
        with self.open_connection() as connection:
            with connection.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
                cur.copy_from(file, table)