def create_array_elem(elem, sql_datatype, conn_info): if elem is None: return None with post_db.open_connection(conn_info) as conn: with conn.cursor() as cur: if sql_datatype == "bit[]": cast_datatype = "boolean[]" elif sql_datatype == "boolean[]": cast_datatype = "boolean[]" elif sql_datatype == "character varying[]": cast_datatype = "character varying[]" elif sql_datatype == "cidr[]": cast_datatype = "cidr[]" elif sql_datatype == "citext[]": cast_datatype = "text[]" elif sql_datatype == "date[]": cast_datatype = "text[]" elif sql_datatype == "double precision[]": cast_datatype = "double precision[]" elif sql_datatype == "hstore[]": cast_datatype = "text[]" elif sql_datatype == "integer[]": cast_datatype = "integer[]" elif sql_datatype == "bigint[]": cast_datatype = "bigint[]" elif sql_datatype == "inet[]": cast_datatype = "inet[]" elif sql_datatype == "json[]": cast_datatype = "text[]" elif sql_datatype == "jsonb[]": cast_datatype = "text[]" elif sql_datatype == "macaddr[]": cast_datatype = "macaddr[]" elif sql_datatype == "money[]": cast_datatype = "text[]" elif sql_datatype == "numeric[]": cast_datatype = "text[]" elif sql_datatype == "real[]": cast_datatype = "real[]" elif sql_datatype == "smallint[]": cast_datatype = "smallint[]" elif sql_datatype == "text[]": cast_datatype = "text[]" elif sql_datatype in ("time without time zone[]", "time with time zone[]"): cast_datatype = "text[]" elif sql_datatype in ("timestamp with time zone[]", "timestamp without time zone[]"): cast_datatype = "text[]" elif sql_datatype == "uuid[]": cast_datatype = "text[]" else: # custom datatypes like enums cast_datatype = "text[]" sql_stmt = """SELECT $stitch_quote${}$stitch_quote$::{}""".format(elem, cast_datatype) cur.execute(sql_stmt) res = cur.fetchone()[0] return res
def register_type_adapters(conn_config): """ //todo doc needed """ with post_db.open_connection(conn_config) as conn: with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur: # citext[] cur.execute( "SELECT typarray FROM pg_type where typname = 'citext'") citext_array_oid = cur.fetchone() if citext_array_oid: psycopg2.extensions.register_type( psycopg2.extensions.new_array_type( (citext_array_oid[0], ), 'CITEXT[]', psycopg2.STRING)) # bit[] cur.execute("SELECT typarray FROM pg_type where typname = 'bit'") bit_array_oid = cur.fetchone()[0] psycopg2.extensions.register_type( psycopg2.extensions.new_array_type((bit_array_oid, ), 'BIT[]', psycopg2.STRING)) # UUID[] cur.execute("SELECT typarray FROM pg_type where typname = 'uuid'") uuid_array_oid = cur.fetchone()[0] psycopg2.extensions.register_type( psycopg2.extensions.new_array_type((uuid_array_oid, ), 'UUID[]', psycopg2.STRING)) # money[] cur.execute("SELECT typarray FROM pg_type where typname = 'money'") money_array_oid = cur.fetchone()[0] psycopg2.extensions.register_type( psycopg2.extensions.new_array_type((money_array_oid, ), 'MONEY[]', psycopg2.STRING)) # json and jsonb # pylint: disable=unnecessary-lambda psycopg2.extras.register_default_json(loads=lambda x: str(x)) psycopg2.extras.register_default_jsonb(loads=lambda x: str(x)) # enum[]'s cur.execute( "SELECT distinct(t.typarray) FROM pg_type t JOIN pg_enum e ON t.oid = e.enumtypid" ) for oid in cur.fetchall(): enum_oid = oid[0] psycopg2.extensions.register_type( psycopg2.extensions.new_array_type( (enum_oid, ), 'ENUM_{}[]'.format(enum_oid), psycopg2.STRING))