How to join two strings as two strings seperated for a SQL command in Python?

Question

Probably, my question sounds strange! However, I need this SQL query in Python:

cursor.execute("SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = 'TEST' ")

Since 'TEST' schema changes I need a query like that:

schema_name = 'TEST'
cursor.execute(" SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = " + str(schema_name))

But the output of this line will be:

' SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = TEST'

Not:

" SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = 'TEST' "

What is needed.

I would be appreciated for your help.

timgeb · Accepted Answer · 2017-12-27 16:27:20Z

2

this is rather basic SQL practice. NEVER concatenate the string like that.

for your case the best way to do it is just:

schema_name = 'TEST'
cursor.execute("SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = %s", schema_name)

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

edited Dec 27, 2017 at 16:27

timgeb

79.2k20 gold badges129 silver badges150 bronze badges

answered Dec 27, 2017 at 16:07

fahyik yong

1416 bronze badges

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

6 Comments

timgeb Over a year ago

This answer is better than mine.

Vahid SJ Over a year ago

Thank you for your answer, but I receive this error: hdbcli.dbapi.ProgrammingError: (0, 'Invalid parameters : execute(SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = %s, TEST, {})')

fahyik yong Over a year ago

@Vahid which library are you using? for different libraries sometimes they use different placeholders. so instead of %s, it might be ? or {}.

Vahid SJ Over a year ago

@fahyikyong Regardless of library, When I run this code in Python 3.6: >> schema_name = 'TEST', >> "SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = %s", schema_name, output is: ('SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = %s', 'TEST')

Vahid SJ Over a year ago

But when I run this one: >> schema_name = " 'TEST' ", >> "SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = {} ".format(schema_name), output is: "SELECT TABLE_NAME AS TABLE FROM M_CS_TABLES WHERE SCHEMA_NAME = 'TEST' "

|

timgeb · Accepted Answer · 2017-12-27 16:04:18Z

1

You could wrap 'TEST' in another pair of quotes, i.e. use '"TEST"'.

Better yet, insert 'TEST' by using str.format.

>>> schema_name = 'TEST'
>>> 'blablabla "{}"'.format(schema_name)
'blablabla "TEST"'

answered Dec 27, 2017 at 16:04

timgeb

79.2k20 gold badges129 silver badges150 bronze badges

1 Comment

Vahid SJ Over a year ago

I just add it to your answer: >>> schema_name = 'TEST' >>> "blablabla '%s' " %schema_name , it has also the same output: "blablabla 'TEST' "

Collectives™ on Stack Overflow

How to join two strings as two strings seperated for a SQL command in Python?

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related