CODEWITHSUNDEEP
pythonmysql-python
null
null

Reputation: 1217

Python MySQLdb: Syntax Error when create table

The code is very simple, I just directly run it from console, meanwhile, spider.table_name = 'crawler'

import MySQLdb
import scrapy

print (spider.table_name)  # >> 'crawler'

db = MySQLdb.connect(........)
db.set_character_set('utf8')
mysql = db.cursor()

sql = "CREATE TABLE %s like r_template;"
mysql.execute(sql, (spider.table_name, ))
db.commit()

But I got Syntax Error:

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''crawler' like r_template' at line 1")

It seems that the actual sql sentence that being executed was: 

CREATE TABLE 'crawler' like r_template

How is that single quote '' generated? How to prevent it from happening?

Then, I tried in more simple way:

mysql.execute(sql, ('crawler', ))
mysql.execute("CREATE TABLE %s like r_template", ('crawler', ))

errors still happened.

Upvotes: 1

Views: 695

Answers (2)

fralau
fralau

Reputation: 3819

You accidentally opened the door to a mysterious and adventurous world. My suggestion is: don't close that door before having a look.

The problem is that your are trying to pass argument for a placeholder "on the left side", but your interface with MySQL works only on the "right side". Placeholders are used for values, not for field names or tables.

The superficial explanation

Let me start with another example.

It is legal to write:

where field = %s

and if the variable is a string, your PEP 249-compliant interface will correctly interpret it: think of it as "putting quotes arounds it" (though it's NOT what it does, otherwise it would open the door to SQL injections; but that will illustrate the point).

That's on the right side of the equality.

But if you write:

where %s = 5

with a value 'my_field', it will not work, because it is on the left side. This is not part of the interface.

As you said if you applied the same logic, it would "put quotes around it", so you would get:

where 'my_field' = 5

it apparently doesn't make sense, because you get quotes where you didn't expect them (caution: again, that's not what happens, but it illustrates the point). It doesn't work, yet those quotes are what you should get if you followed your own logic. There is a contradiction, so something is apparently wrong.

But wait!

A deeper explanation

it is important to understand that with PEP 249 interfaces, the arguments for placeholders are NOT converted into strings and then put into a string query. They are translated into their equivalents (int, etc.) and treated at a lower level (within a parsing tree or some similar structure, I suppose).

The mechanism has been specified for converting the arguments into values. It was not designed for variable identifiers, such as fields or tables (which is a more advanced use case).

Saying that an "identifier is a variable" is quite an advanced idea... I gets you into the wonderful world of higher-order programming.

Could PEP249 be extended to do that? In theory yes, but this is not an open and shut question.

Solution

In the mean time, you are left with only one option: interpolate your string query before you give it to the SQL engine.

sql = "CREATE TABLE %s like r_template;" % 'crawler'

I can imagine the shudders of horror in people around you (don't open that door!). But to the best of my knowledge, that's what you have to do if you really want to have a variable table name.

At that point, you may want to ask yourself why you want to have a variable table name? Did you do that as a lazy workaround for something else? In that case, I would return to the beaten path and forget about making tables or fields variable.

I see, however, two use cases where variable tables or fields are perfectly legitimate: for database administration or for building a database framework.

If that is your case, just use your string interpolation with care, to avoid unintentional SQL injections. You will have to deal wit different issues with whitespaces, special characters, etc.; a good practice will be to "quote" your field or table names, e.g. in standard MySQL:

`table accentuée`

whereas, with ANSI quoting:

"table accentuée"

(As you see, you were not that far off!)

Also be careful to strip off things that could throw the interpreter off, like semicolons.

Anyway, if you want to do that, you will need to navigate out of sight of the coast, straight toward the sunset. You are on the threshold of the hero's journey to the left side. You will enjoy the adventure, as long as you accept that there will be no lifeguard to come to your rescue.

Upvotes: 3

vi_ral
vi_ral

Reputation: 369

Use .format instead of %s this will allow you to avoid the single quotes in your query. For Example:

my_sql_query = "CREATE TABLE {}".format(table_name)
mysql.execute(my_sql_query)

That should work :)

Upvotes: 0

Related Questions