执行脚本删除数据库下的数据表:
from diserver.statsdb import db,dbschema
def dropTable(Time,deviceid):
tablename = 'di_idx_file_' + Time
print(tablename)
stat_db = db.StatsDatabase()
with stat_db.getConnection(deviceid) as conn:
conn.execute('USE {}'.format(deviceid)) #这步出错
conn.execute('CREATE TABLE IF NOT EXISTS {} (id INT)'.format(tablename))
#conn.execute('DROP TABLE IF EXISTS {}'.format(tablename))
#dropTable('2019_2','E8BBD1B9ECDB4FD9A858') #成功
dropTable('2019_2','887716E9DE114E068F10') #失败
报错信息如下:(mysql.connector.errors.ProgrammingError) 1064 (42000)
File "/opt/poc/lib/python2.7/site-packages/mysql/connector/connection.py", line 494, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "/opt/poc/lib/python2.7/site-packages/mysql/connector/connection.py", line 396, in _handle_result
raise errors.get_exception(packet)
sqlalchemy.exc.ProgrammingError: (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 '887716E9DE114E068F10' at line 1 [SQL: 'USE 887716E9DE114E068F10;']
原因:与mysq保留关键字冲突
解决办法:使用反引号(tab键上的那个键)将表名扩起来 conn.execute('USE `{}`;'.format(deviceid))