Converting SQLite database column values to strings and concatenating

Question

I have a database with the following format.

(1, 'Kristen', 'Klein', '2002-11-03', 'North Cynthiafurt', 'AZ', '50788')

I am trying to strip away the first and last name values and pass them to a function to concatenate them as strings. "Kristen Klein" in this case.

I use a query such as:

query_first = db.select([customer.columns.first_name])
proxy_first = connection.execute(query_first)
result_first = proxy_first.fetchall()
print(result_first)

to extract all the first name values and they come out like this: [('Kristen',), ('April',), ('Justin',)]

I use an identical one for the last names and get an identical output.

This syntax is confusing to me as it appears to be a dictionary (?). How do I convert these to strings so that I may concatenate them into a cohesive name?

This [('Kristen',), ('April',), ('Justin',)] - is a list of tuples. — kite
– kite, Commented Oct 10, 2021 at 6:04
If you are familiar with sqlite, you can adjust your query to get the values of both first and last name like this SELECT firstname,lastname FROM tablename; and you will have a output like this [('firstname', 'lastname'), ('Kristen', 'Klein'), ... ] — kite
– kite, Commented Oct 10, 2021 at 6:07
Revise the question whether you are using sqlalchemy please — MattSom
– MattSom, Commented Nov 23, 2022 at 9:25

kite · Accepted Answer · 2021-10-10 12:30:29Z

1

This [('Kristen',), ('April',), ('Justin',)] - is a list of tuples. If you are confused by the trailing comma after string, because it is required to distinguish it as a tuple for single element tuple's. Find out the full info here in python wiki.

I guess you were using sqlalchemy library to connect to the db. If so by selecting the last_name with your first_name would provide you with a result_set list at the end which is iterable in a for loop. So, by concatenating each tuple would give you the full name. Please find the changes below,

#input
query = db.select([customer.columns.first_name, customer.columns.last_name])
result_proxy = connection.execute(query)
result_set = result_proxy.fetchall()

for row in result_set:
    print(' '.join(row))

#output
Kristen Klein
Peter Parker
Tony Stark
...

edited Oct 10, 2021 at 12:30

answered Oct 10, 2021 at 11:54

kite

5514 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yaakov Bressler · Accepted Answer · 2021-10-10 12:24:23Z

0

You can also concatenate on the query level:

from sqlalchemy.sql import  functions as func

query = db.select(
    func.concat(
        customer.first_name, ' ', customer.last_name
    )
)
results = connection.execute(query).all()

Or write raw SQL:

raw_sql = """
    SELECT
        first_name || ' ' || last_name
    FROM
        customer
"""

results = connection.execute(raw_sql).all()

answered Oct 10, 2021 at 12:24

Yaakov Bressler

12.7k5 gold badges66 silver badges96 bronze badges

1 Comment

MattSom Over a year ago

Have you tried this yourself? SQLite does not understand func.concat

MattSom · Accepted Answer · 2022-11-23 09:30:30Z

0

There are some SQLAlchemy utilizing answers here. In that case:

SQLite don't understand func.concat, you have to use string addition operators.

from sqlalchemy import String
from sqlalchemy.sql.expression import cast

query = db.select(cast(customer.columns.first_name, String) + " " + cast(customer.columns.last_name, String))

answered Nov 23, 2022 at 9:30

MattSom

2,4495 gold badges37 silver badges63 bronze badges

Collectives™ on Stack Overflow

Converting SQLite database column values to strings and concatenating

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related