python sqlalchemy get column names dynamically?

Related searches
#!/usr/bin/env python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine

connection = create_engine('mysql://user:passwd@localhost:3306/db').connect()

result = connection.execute("select * from table")
for v in result:
        print v['id']
        print v['name']
connection.close()

how i can get TABLES COLUMNS NAMES dynamically? in this case id and name

You can either find the columns by calling result.keys() or you can access them through calling v.keys() inside the for loop.

Here's an example using items():

for v in result:
    for column, value in v.items():
        print('{0}: {1}'.format(column, value))

Get column names Dynamically with SQLAlchemy, You can use Device.__table__.columns to get the list of columns in your Device model in your code. These are Column objects and you can get the name by� Get Primary Key column name from table in sqlalchemy (Core) all sorts of information from a table in a more dynamic fashion. questions tagged python

Most direct solution

Can't get any simpler than a one-liner solution using list comprehension. It also is the most direct method:

[col for col in result.keys()]
# return: ['id', 'name']

@Saul's answer also works, but you'll need to be careful about iterating over only the first element through each cursor.description, lest you get a bunch of None in each tuple of the returned list.

It also is less-efficient, because you need to iterate through the ResultProxy, access the cursor.description attribute and for each of them only retrieve the element at index 0.

Using timeit in Python with 500,000 iterations showed the speed difference (0.016 vs 0.011):

connection = create_engine('sqlite:///rcsample.db').connect()
result = connection.execute("select * from response")
def cursfunc():
    return [ i[0] for i in result.cursor.description ]
print(timeit.timeit("cursfunc()", setup="from __main__ import cursfunc", number=500000))
# return: 0.01606178

While the proposed solution completes in ~30% less time:

connection = create_engine('sqlite:///rcsample.db').connect()
result = connection.execute("select * from response")

def keysfunc():
    return [col for col in result.keys()]
print(timeit.timeit("keysfunc()", setup="from __main__ import cursfunc", number=500000))
# return: 0.01097001

In fact, my suspicion is that the time difference could be greater on a table with more columns than the obviously simplified example above.

In Practice: keys and values

In practice, you'd probably want to print both the key and values dynamically. There are two ways you can go about it. The first:

results = conn.execute('SELECT * FROM salesperson')
[{column:value for column, value in result.items()} for result in results]
# returns: [{'id': 1, 'name': 'John Doe'}, {'id': 2, 'name': 
# 'Margaret'}, {'id': 3, 'name': 'Anna'}]

Alternatively using unpacking:

rows = conn.execute('SELECT * FROM salesperson LIMIT 2').fetchall()
print([{**row} for row in rows])
# returns: [{'id': 1, 'name': 'John Doe'}, {'id': 2, 'name': 'Margaret'}]

Both of these methods are direct and pythonic, while also exempt the programmer from having to specify (or know beforehand) the columns names explicitly.

How to give column name dynamically from string , I want to create query in sql alchemy filter, but column is(dynamic) in Just use getattr standard python library function to get an attribute by name: col_name� method sqlalchemy.engine.reflection.Inspector. get_columns (table_name, schema = None, ** kw) ¶ Return information about columns in table_name. Given a string table_name and an optional string schema, return column information as a list of dicts with these keys: name - the column’s name. type - the type of this column; an instance of TypeEngine

something like this

headers=[ i[0] for i in result.cursor.description ]

same question here return column names from pyodbc execute() statement

How to give column name dynamically from , Dynamically construct filters based on string input using SQLAlchemy, Here I am going to write a dynamic filter. This filter is made for python 3. It will take query� def update_columns(table_name, pids, column_to_update): ''' 1. table_name: a string denoting the name of the table to be updated 2. pid: a list of primary ids 3. column_to_update: a string representing the name of the column that will be flagged. Sometimes the name can be is_processed or is_active and several more other columns.

How to give column name dynamically from string , How to give column name dynamically from string variable in sql alchemy filter? I want to create query in sql alchemy filter, but column is(dynamic) in 1 Year ago. Just use getattr standard python library function to get an attribute by name:. from sqlalchemy import MetaData from sqlalchemy import Table from sqlalchemy import Column from sqlalchemy import Integer, String meta = MetaData t = Table ('ex_table', meta, Column ('id', Integer, primary_key = True), Column ('key', String), Column ('val', Integer)) # Get Table Name print (t. name) # Get Columns print (t. columns. keys

SQLAlchemy — pysheeet, Collect useful snippets of SQLAlchemy. python sqlalchemy_url.py Integer, primary_key=True), Column('key', String), Column('val', Integer)) # Get Table Name print(t.name) # Get Columns Create tables with dynamic columns (Table )�. How to get column names in Pandas dataframe Last Updated: 05-12-2018 While analyzing the real datasets which are often very huge in size, we might need to get the column names in order to perform some certain operations.

To read sql table into a DataFrame using only the table name, without executing any query we use read_sql_table() method in Pandas. This function does not support DBAPI connections. List of column names to parse as dates. Dict of {column_name: format string} where format string is strftime