如何使用python翻译PostgreSQL OID [英] How do I translate PostgreSQL OID using python

问题描述

我在使用psycopg2 Python模块时遇到了麻烦。我写了一个小代码，使用psycopg2模块从PostgreSQL表中提取一些信息。我想知道表中各列的数据类型：

I am having bit of trouble with the psycopg2 Python module. I wrote a small code to extract some information from PostgreSQL table using psycopg2 module. I want to know the data type for each columns in the table:

import os, psycopg2, getpass, sys, string

userpass = getpass.getpass()

# Connect to the database
conn = psycopg2.connect("host=hostname dbname=db user=username password=%s" %  (userpass))
cur = conn.cursor()
cur.execute("SELECT * FROM database.table;")

fieldTypes = [desc[1] for desc in cur.description]

print fieldTypes

当我执行此命令时代码，我得到一个整数列表（即OID）。有没有一种方法可以将其转换为更易理解的内容（例如'str'，'int'，'bool'等）。

When I execute this code, I get a list of integers (i.e. OIDs). Is there a way to convert this into something that's more understandable (e.g. 'str','int','bool' and so forth).

推荐答案

只要提供文本 current / interactive / datatype-oid.html rel = nofollow> OID（对象标识符）实际上是 regtype （已注册类型的OID子类型），就像您从函数 pg_typeof（ ） 。

You can convert the "OID" to text by simply casting - provided the OID (Object Identifier) is actually a regtype (the OID-subtype for registered types) like you would get from the function pg_typeof().

Postgres通常会显示数据类型为 regtype 作为文本发送给用户。示例：

Postgres will normally display values of the data type regtype as text to the user. Example:

SELECT pg_typeof('2013-1-1'::date);
 pg_typeof
-----------
 date

内部是OID：

SELECT pg_typeof('2013-1-1'::date)::oid;
 pg_typeof
-----------
      1082

如果您的客户没有这样做，则可以通过显式强制转换：

If your client does not do the same you can force it with an explicit cast:

SELECT pg_typeof('2013-1-1'::date)::text;
SELECT 1082::regtype::text;

获取所有列的类型从系统目录中

目前尚不清楚如何实际检索类型。考虑以下查询以获取完整的信息：

Get types of all columns from system catalog

It's unclear how you actually retrieve the types. Consider this query to get full information:

SELECT attname
     , atttypid::regtype AS base_type
     , format_type(atttypid, atttypmod) AS full_type
FROM   pg_catalog.pg_attribute
WHERE  attrelid = 'public.tbl'::regclass  -- your table name here
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

  attname   |          base_type          |         full_type
------------+-----------------------------+-----------------------------
 age_id     | integer                     | integer
 age        | text                        | text
 ageabk     | character                   | character(2)
 foo        | boolean                     | boolean
 log_up     | timestamp without time zone | timestamp without time zone

请注意， format_type（..） > 显示包含修饰符的类型。

Note that format_type(..) displays the type including modifiers.

这篇关于如何使用python翻译PostgreSQL OID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！