From "Programming Python" by Mark Lutz:
curs.execute("Select * FROM people LIMIT 0")
colnames = [desc[0] for desc in curs.description]
Top answer 1 of 11
383
From "Programming Python" by Mark Lutz:
curs.execute("Select * FROM people LIMIT 0")
colnames = [desc[0] for desc in curs.description]
2 of 11
104
Another thing you can do is to create a cursor with which you will be able to reference your columns by their names (that's a need which led me to this page in the first place):
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
Top answer 1 of 2
1
def get_query_results_as_list_of_dicts(query):
"""
runs a query and returns the result as a dict rather than a tuple
:param query: SQL formatted string
:return: list of dictionary objects. one for each result returned from the database
"""
# Execute the query
cur.execute(query)
# Fetch all the rows as a list of dictionaries
list_of_dicts = [dict(row) for row in cur.fetchall()]
return list_of_dicts
2 of 2
0
You should use the RealDictCursor when creating the cursor for psycopg2.
import psycopg2
from psycopg2.extras import RealDictCursor
# Connect to the database
db = psycopg2.connect(
dbname="your_dbname",
user="your_username",
password="your_password",
host="your_host",
port="your_port"
)
# Create a cursor using RealDictCursor
cursor = db.cursor(cursor_factory=RealDictCursor)
query = f"SELECT * FROM your_table"
# Execute the query
cursor.execute(query)
data = cursor.fetchall()
for row in data:
print(row['id'])
print(row['column_name'])
# Close the cursor and connection
cursor.close()
db.close()
ZetCode
zetcode.com › python › psycopg2
Python PostgreSQL proramming with psycopg2 module
January 29, 2024 - Next we print all rows from the cars table with their column names. ... #!/usr/bin/python import psycopg2 con = psycopg2.connect(database='testdb', user='postgres', password='s$cret') with con: cur = con.cursor() cur.execute('SELECT * FROM cars') col_names = [cn[0] for cn in cur.description] rows = cur.fetchall() print(f'{col_names[0]} {col_names[1]} {col_names[2]}')
PostgreSQL Wiki
wiki.postgresql.org › wiki › Using_psycopg2_with_PostgreSQL
Using psycopg2 with PostgreSQL - PostgreSQL wiki
# Note that even though we've returned the columns by name we can still # access columns by numeric index as well - which is really nice. cursor.execute('SHOW work_mem') # Call fetchone - which will fetch the first row returned from the # database. memory = cursor.fetchone() # access the column by numeric index: # even though we enabled columns by name I'm showing you this to # show that you can still access columns by index and iterate over them. print "Value: ", memory[0] # print the entire row print "Row: ", memory if __name__ == "__main__": main() If you have an extremely large result set
GeeksforGeeks
geeksforgeeks.org › get-column-name-and-column-type-with-python-psycopg2
Get Column name and Column type with Python psycopg2 | GeeksforGeeks
September 2, 2024 - Below code connects to the PostgreSQL database, retrieves column names and data types from the information_schema.columns for a specified table, and prints them. It then closes the cursor and database connection. ... import psycopg2 # Connect to your PostgreSQL database conn = psycopg2.connect( dbname="your_database_name", user="your_username", password="your_password", host="localhost", port="5432" ) # Create a cursor object cur = conn.cursor() # Create a table with various data types create_table_query = """ CREATE TABLE product ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(255), price DECIMAL(10, 2), stock_quantity INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """ cur.execute(create_table_query) conn.commit() print("Table created successfully") # Close the cursor and connection cur.close() conn.close()
GeeksforGeeks
geeksforgeeks.org › get-column-names-from-postgresql-table-using-psycopg2
Get column names from PostgreSQL table using Psycopg2 | GeeksforGeeks
September 14, 2021 - This article is an illustration of how to extract column names from PostgreSQL table using psycopg2 and Python.
Top answer 1 of 2
35
You need to use RealDictCursor, then you can access the results like a dictionary:
import psycopg2
from psycopg2.extras import RealDictCursor
connection = psycopg2.connect(user="...",
password="...",
host="...",
port="...",
database="...",
cursor_factory=RealDictCursor)
cursor = connection.cursor()
cursor.execute("SELECT * FROM user")
users = cursor.fetchall()
print(users)
print(users[0]['user'])
Output:
[RealDictRow([('user', 'dbAdmin')])]
dbAdmin
2 of 2
6
no need to call fetchall() method, the psycopg2 cursor is an iterable object you can directly do:
cursor.execute("SELECT * FROM user")
for buff in cursor:
row = {}
c = 0
for col in cursor.description:
row.update({str(col[0]): buff[c]})
c += 1
print(row["id"])
print(row["first_name"])
print(row["last_name"])
Top answer 1 of 6
17
If what you want is a dataframe with the data from the db table as its values and the dataframe column names being the field names you read in from the db, then this should do what you want:
import psycopg2 as pq
cn = pq.connect('dbname=mydb user=me')
cr = cn.cursor()
cr.execute('SELECT * FROM test1;')
tmp = cr.fetchall()
# Extract the column names
col_names = []
for elt in cr.description:
col_names.append(elt[0])
# Create the dataframe, passing in the list of col_names extracted from the description
df = pd.DataFrame(tmp, columns=col_names)
2 of 6
13
The column names are available as cr.description[0][0], cr.description[1][0], etc. If you want it in exactly the format you show, you need to do some work to extract it and stick it in front of the result set.
PYnative
pynative.com › home › python › databases › python select from postgresql table using psycopg2
Python Select from PostgreSQL Table using psycopg2
March 9, 2021 - ... Execute the select query using the cursor.execute() method. ... After successfully executing a Select operation, Use the fetchall() method of a cursor object to get all rows from a query result. it returns a list of rows.
Johnnn
johnnn.tech › home › questions › building a psycopg2 query using a list of the column names to fetch
Building a psycopg2 query using a list of the column names to fetch - Johnnn
July 16, 2021 - which is not valid in PSQL because 1. of the brackets, and 2. of the single quoted column names. We'd like to figure out a way to get rid of these. In between, we went back to the doc and remembered this: https://www.psycopg.org/docs/usage.html · Ooops! So we refactored it this way: sql_query = (f"""SELECT %s FROM product p INNER JOIN owner o ON p.id = o.product_id WHERE p.id = %s AND (o.dateof_purchase IS NOT NULL OR o.state = 'checked_out' );""") try: with psycopg2.connect(**DB_PARAMS) as conn: with conn.cursor(cursor_factory=DictCursor) as curs: # passing a tuple as it only accept one more argument after the query!
Safe Community
community.safe.com › home › forums › fme form › authoring › generate list of column names from database table
Generate list of column names from database table | Community
October 18, 2017 - Execute the SELECT query using a cursor.execute() and use cursor.fetchall() to fetch the result. Iterate over the ResultSet using for loop to get all the columns. Close the cursor and database connection.
Hackers and Slackers
hackersandslackers.com › psycopg2-postgres-python
Psycopg2: PostgreSQL & Python the Old Fashioned Way
January 15, 2019 - These aren't lists of values, they're a new data structure unique to Psycopg2! While we see what appears to be a list of values, each value in each row has a key: the name of the column it belongs to. Check it out: def select_rows_dict_cursor(self, query): """Run SELECT query and return dictionaries.""" self.connect() with self.conn.cursor(cursor_factory=DictCursor) as cur: cur.execute(query) for row in cur.fetchall(): print(row['first_name']) Accessing values by column name ·
YouTube
youtube.com › fixitgeek
Python :How do I get a list of column names from a psycopg2 cursor?(5solution) - YouTube
Thanks For watching My video Please Like Share And Subscribe My Channel
Published November 30, 2022 Views 3
YouTube
youtube.com › how to fix your computer
PYTHON : How do I get a list of column names from a psycopg2 cursor? - YouTube
PYTHON : How do I get a list of column names from a psycopg2 cursor? [ Gift : Animated Search Engine : https://www.hows.tech/p/recommended.html ] PYTHON : H...
Published December 6, 2021 Views 121
sqlpey
sqlpey.com › python › top-8-methods-to-retrieve-column-names-using-psycopg2
Top 8 Methods to Retrieve Column Names Using Psycopg2 - …
November 1, 2024 - import psycopg2 DSN = "host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER" with psycopg2.connect(DSN) as connection: with connection.cursor() as cursor: cursor.execute("SELECT field1, field2 FROM table1") column_names = [desc[0] for desc in cursor.description] data_rows = cursor.fetchall() print("Column names: {}".format(column_names))
GitHub
gist.github.com › walkermatt › 061b61229a8614a5b871e7ac35b672da
python_psycopg2_postgis.md · GitHub
March 22, 2017 - import psycopg2 import psycopg2.extras # Create a connection to the database with psycopg2.connect("host=localhost dbname=postgis user=postgres password=postgres") as conn: # Create a regular cursor which will return a list of rows with each row # being a tuple of values in column order with conn.cursor() as cur: # Execute a query and return all rows as a list cur.execute("SELECT ogc_fid, name, st_asewkt(st_centroid(wkb_geometry)) as geom FROM county_region limit 10") rows = cur.fetchall() print print "# Rows as a list:" print print rows colnames = [desc.name for desc in cur.description] print
CSDN
devpress.csdn.net › python › 62fe077b7e66823466192fa5.html
Python psycopg2 postgres select columns including field names_python_Mangs-Python
August 18, 2022 - Hi I'd like to get a table from a database, but include the field names so I can use them from column headings in e.g. Pandas where I don't necessarily know all the field names in advance ... import psycopg2 as pq cn = pq.connect('dbname=mydb user=me') cr = cn.cursor() cr.execute('SELECT * FROM test1;') tmp = cr.fetchall() tmp
Itecnote
itecnote.com › tecnote › python-psycopg2-postgres-select-columns-including-field-names
Python psycopg2 postgres select columns including field ...
온라인 포커 추천 사이트 TOP10을 레이크백·보너스·입출금 기준으로 직접 비교했습니다. 최대 60% 레이크백부터 노디포짓 보너스까지, 2026 년 검증된 해외 포커 플랫폼만 정리했습니다.