Get the names of the data types from the postgresql table on python



  • Used psycopg2 for postgresql. There's this thing.

    cursor.description 
    

    from which only OID type of column data can be obtained. The question is: What's the name of the type? Maybe there's a function that returns the name to OID? I've in the documentation, but I haven't found anything.



  • import psycopg2
    
    if __name__ == '__main__':
        DSN = 'host=HOST port=PORT dbname=DBNAME user=USER password=PASS'
    
        with psycopg2.connect(DSN) as connection:
            with connection.cursor() as cursor:
                cursor.execute("SELECT * FROM TABLE_NAME LIMIT 0")
                for i in cursor.description:
                    cursor.execute("SELECT typname FROM pg_type WHERE oid={oid}".format(oid=i[1]))
                    print("Column name: ", i[0], " Column type: ", cursor.fetchone()[0])
    

    As indicated in the comments above, it is necessary to look in table pg_type. pg_type - service table in pg_catalog. https://www.postgresql.org/docs/current/static/catalogs.html It stores all types of data. This table may be useful if it is necessary to create its own type of data in psycopg2. Details: http://initd.org/psycopg/docs/advanced.html#type-casting-of-sql-types-into-python-objects

    The name of the type of data can also be obtained differently (leasing the mix of compounds and cadets):

    SELECT column_name, data_type FROM information_schema.columns
    

    WHERE table_schema = 'public' AND table_name = TABLE_NAME

    The type-applicable name is in the data_type column.
    https://www.postgresql.org/docs/9.1/static/infoschema-columns.html The second decision has a plus: the type of data is stored as varchar, while pg_type data type is a name. Document quotation:

    The name type exists only for the storage of identifiers in the
    internal system catalogs and is intended not for use by the general
    user.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2