Skip to content

Instantly share code, notes, and snippets.

@ThomasG77
Created September 25, 2021 13:23
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save ThomasG77/f2c843666c84195a872efebcc21f1f9f to your computer and use it in GitHub Desktop.
import csv
import os
from qgis.PyQt.QtCore import QVariant
layer = iface.activeLayer()
uri_text = layer.dataProvider().uri().uri()
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(uri_text, {})
my_cols = [
'table_catalog',
'table_schema',
'table_name',
'column_name',
'ordinal_position',
'column_default',
'is_nullable',
'data_type',
'character_maximum_length',
'character_octet_length',
'numeric_precision',
'numeric_precision_radix',
'numeric_scale',
'datetime_precision',
'interval_type',
'interval_precision',
'character_set_catalog',
'character_set_schema',
'character_set_name',
'collation_catalog',
'collation_schema',
'collation_name',
'domain_catalog',
'domain_schema',
'domain_name',
'udt_catalog',
'udt_schema',
'udt_name',
'scope_catalog',
'scope_schema',
'scope_name',
'maximum_cardinality',
'dtd_identifier',
'is_self_referencing',
'is_identity',
'identity_generation',
'identity_start',
'identity_increment',
'identity_maximum',
'identity_minimum',
'identity_cycle',
'is_generated',
'generation_expression',
'is_updatable'
]
my_table_name = layer.dataProvider().uri().table()
my_query = f"""WITH columns_with_comment AS (
SELECT {','.join(my_cols)}, col_description((table_schema||'.'||table_name)::regclass::oid, ordinal_position) AS column_comment
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = '{my_table_name}'
)
SELECT * FROM columns_with_comment
"""
result = conn.executeSql(my_query)
result = [['' if isinstance(el, QVariant) and el.isNull() else el for el in line] for line in result]
result = [my_cols + ['column_comment']] + result
# my_table_name
home_path = QgsProject.instance().homePath()
with open(os.path.join(home_path, f"{my_table_name}.csv"), "w") as f:
writer = csv.writer(f)
writer.writerows(result)
iface.addVectorLayer(os.path.join(home_path, f"{my_table_name}.csv", f"{my_table_name} metadata", "ogr")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment