How to get value from SQLAlchemy instance by column name?

In this article, we will discuss how to get value from a table in SQL with the help of SQLAlchemy in Python.

Database used:

Installation

pip install SQLAlchemy pymysql

Note: pymysql is a dependency of sqlalchemy which users need to install so as to run our program.

Get value by column name

The given task can be performed by first creating an engine with sqlalchemy, connecting with the database, and executing an SQL query with the connection. The SQL query will contain the name of the column/columns whose values we want and then we will get a result object. The result object will give us all the values when we call fetchall method of the object instance.

Python3

from sqlalchemy import create_engine

user, password, host, database =\
'root', '123', 'localhost', 'geeksforgeeks'

engine = create_engine(
    url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')

connection = engine.connect()

# column name whose values we want
table_name = 'student'
column_name = 'name,dob'

# creating the sql query
query = f'SELECT {column_name} FROM {table_name}'

# running the query
result = connection.execute(query)

# fetching all the result and storing in
# values variable
values = result.fetchall()

# printing the output
print(values)

Output:

Output

Example 2:

Here, if the user wants to get values of multiple columns then you can specify the column names in the query by separating them with a comma (,).

Python3

from sqlalchemy import create_engine

user, password, host, database =\
'root', '123', 'localhost', 'geeksforgeeks'

engine = create_engine(
    url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')

connection = engine.connect()

table_name = 'student'

# column names whose values we want
column_names = 'name,dob'

# creating the sql query
query = f'SELECT {column_names} FROM {table_name}'

# running the query
result = connection.execute(query)

# fetching all the result and storing in
# values variable
values = result.fetchall()

# printing the output
print(values)

Output:

Output