python MySQL连接器和参数化查询 [英] python MySQL connector and parameterized query

问题描述

Python 3.6/MySQL 5.6

Python 3.6 / MySQL 5.6

虽然我已经在其他编码语言中使用 MySQL 有一段时间了，但我对 Python 还是很陌生.在开发环境中，我想删除特定数据库中的所有表.我可以删除数据库，但托管服务提供商锁定了一些 MySQL 控件，因此可以从命令行或代码删除数据库"，但只能通过其管理 Web 面板创建它们.这是一种耗时的痛苦.我可以更轻松地从命令行或代码删除/创建表.

I am still pretty new to python though I've used MySQL in other coding languages for sometime. In a dev environment I want to drop all tables in a particular database. I could drop the database but the hosting provider locks down some MySQL controls so dropping "databases" is possible from the command line or code but creating them is only allowed through their management web panel. That is a time consuming pain. I can drop/create the tables from command line or code much easier.

我写了一个 python 脚本，当我想清理/重新启动项目时，我可以从 Makefile 调用它.

I wrote a python script that I can call from a Makefile when i want to clean/restart the project.

import os
import mysql.connector.django

DBI = mysql.connector.connect(
    option_files=os.path.join(os.path.expanduser("~"), ".my.cnf"),
    option_groups="membersdev"
)

cursorFind = DBI.cursor(buffered=True)
cursorDrop = DBI.cursor(buffered=True)

query = """
select TABLE_NAME
from information_schema.TABLES
where TABLE_SCHEMA = %s
"""
cursorFind.execute(query, ('dev_site_org',))

query2 = "DROP TABLE IF EXISTS %s"

for tableName in cursorFind.fetchall():
    cursorDrop.execute(query2, tableName)

cursorDrop.close()
cursorFind.close()

DBI.close()

我很确定query2"在带有参数的语法上是正确的.我认为 cursorDrop.execute(query2, tableName) 是正确的，因为 tableName 是一个元组；但是，我不断收到异常和堆栈跟踪:

I am pretty sure that "query2" is syntactically correct with a parameter. I think that cursorDrop.execute(query2, tableName) is correct since tableName is a tuple; but, I keep getting the exception and stack trace:

Traceback (most recent call last):
  File "/home/me/.pyenv/versions/3.6.3/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 377, in cmd_query
    raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: 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 ''My_First_Table'' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "misc_scripts/db_delete.py", line 35, in <module>
    cursorDrop.execute(query2)
  File "/home/me/.pyenv/versions/3.6.3/lib/python3.6/site-packages/mysql/connector/cursor_cext.py", line 264, in execute
    raw_as_string=self._raw_as_string)
  File "/home/me/.pyenv/versions/3.6.3/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 380, in cmd_query
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''My_First_Table'' at line 1

从选择结果元组中访问表名是否需要做一些特殊的事情?我是否必须对查询进行排序或以不同方式执行?是否有我遗漏的准备"声明?

Is there something special I need to do to access the table name from the select result tuple? Do I have to order the queries or the executes differently? Is there a statement "prepare" that I am missing?

推荐答案

在 MySQL 中，schema 对象与 SQL 参数不同，有不同的引用规则，schema 对象的引号是反引号 (`):

In MySQL, schema objects, unlike SQL parameters, have a different quotation rule, the quote mark for schema object is the backtick (`):

标识符可以带引号或不带引号.如果标识符包含特殊字符或者是保留字，则在引用它时必须引用它.(例外:限定名称中的句点后面的保留字必须是标识符，因此不需要用引号引起来.)第 9.3 节关键字和保留字"中列出了保留字.

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.) Reserved words are listed at Section 9.3, "Keywords and Reserved Words".

...

标识符引号字符是反引号(`):

The identifier quote character is the backtick (`):

你可以像这样修改你的代码:

you could modify your code like this:

query2 = "DROP TABLE IF EXISTS `%s`" 
...
    cursorDrop.execute(query2 % tableName)

查看关于 MySQL 文档的更多信息.

see more on MySQL doc.

这篇关于python MySQL连接器和参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！