我想要一种从选定的列名直接生成列标签的通用方法,并且记得python的psycopg2模块支持这一特性。
当前回答
如果你想从db查询中获得一个命名元组obj,你可以使用下面的代码片段:
from collections import namedtuple
def create_record(obj, fields):
''' given obj from db returns named tuple with fields mapped to values '''
Record = namedtuple("Record", fields)
mappings = dict(zip(fields, obj))
return Record(**mappings)
cur.execute("Select * FROM people")
colnames = [desc[0] for desc in cur.description]
rows = cur.fetchall()
cur.close()
result = []
for row in rows:
result.append(create_record(row, colnames))
这允许您访问记录值,如果他们是类属性,即。
记录。id、记录。other_table_column_name等等。
或者更短
from psycopg2.extras import NamedTupleCursor
with cursor(cursor_factory=NamedTupleCursor) as cur:
cur.execute("Select * ...")
return cur.fetchall()
其他回答
摘自Mark Lutz的《Programming Python》:
curs.execute("Select * FROM people LIMIT 0")
colnames = [desc[0] for desc in curs.description]
要在单独的查询中获得列名,可以查询information_schema。表列。
#!/usr/bin/env python3
import psycopg2
if __name__ == '__main__':
DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'
column_names = []
with psycopg2.connect(DSN) as connection:
with connection.cursor() as cursor:
cursor.execute("select column_name from information_schema.columns where table_schema = 'YOUR_SCHEMA_NAME' and table_name='YOUR_TABLE_NAME'")
column_names = [row[0] for row in cursor]
print("Column names: {}\n".format(column_names))
要在相同的查询中获取列名作为数据行,您可以使用游标的description字段:
#!/usr/bin/env python3
import psycopg2
if __name__ == '__main__':
DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'
column_names = []
data_rows = []
with psycopg2.connect(DSN) as connection:
with connection.cursor() as cursor:
cursor.execute("select field1, field2, fieldn from table1")
column_names = [desc[0] for desc in cursor.description]
for row in cursor:
data_rows.append(row)
print("Column names: {}\n".format(column_names))
如果你想获得一个已经关联列标头的pandas数据帧,试试这个:
import psycopg2, pandas
con=psycopg2.connect(
dbname=DBNAME,
host=HOST,
port=PORT,
user=USER,
password=PASSWORD
)
sql = """
select * from x
"""
d = pandas.read_sql_query(sql,con)
con.close()
print(type(d))
print(pandas.DataFrame.head(d))
我注意到你必须在查询后使用cursor.fetchone()来获取cursor.description中的列列表(即在[desc[0] for desc in curs.description])
你可以做的另一件事是创建一个游标,你可以通过它们的名字引用你的列(这是一个需要,导致我在第一个地方到这个页面):
import psycopg2
from psycopg2.extras import RealDictCursor
ps_conn = psycopg2.connect(...)
ps_cursor = psql_conn.cursor(cursor_factory=RealDictCursor)
ps_cursor.execute('select 1 as col_a, 2 as col_b')
my_record = ps_cursor.fetchone()
print (my_record['col_a'],my_record['col_b'])
>> 1, 2
推荐文章
- 证书验证失败:无法获得本地颁发者证书
- 当使用pip3安装包时,“Python中的ssl模块不可用”
- 无法切换Python与pyenv
- Python if not == vs if !=
- 如何从scikit-learn决策树中提取决策规则?
- 为什么在Mac OS X v10.9 (Mavericks)的终端中apt-get功能不起作用?
- 将旋转的xtick标签与各自的xtick对齐
- 为什么元组可以包含可变项?
- 如何合并字典的字典?
- 如何创建类属性?
- 不区分大小写的“in”
- 在Python中获取迭代器中的元素个数
- 解析日期字符串并更改格式
- 使用try和。Python中的if
- 如何在Python中获得所有直接子目录