查询中的python mysql错误 [英] python mysql error in query

查看:72
本文介绍了查询中的python mysql错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想生成一个动态表:

columnames=[element[0] for element in bufferdata['data'] ]
for index,element in enumerate(columnames):
        columnames[index]=re.sub("[(%./)-]","",element)
    tuple(columnames)
    querycreatetable='''CREATE TABLE test (ID INT AUTO_INCREMENT,name VARCHAR(50),symbol VARCHAR(10),sector VARCHAR(50),
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                           %s FLOAT,%s FLOAT,%s FLOAT
                                           ) 
                        '''
    try:
        self.cursor.execute(querycreatetable,columnames)
    except MySQLdb.ProgrammingError, e:
        try:
            print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
        except IndexError:
            print "MySQL Error: %s" % str(e)

但我收到此错误:MySQL 错误 [1064]:您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,了解在第 2 行的以百万计的销售额"FLOAT、每股收益"FLOAT、PE Ratio TTM" FLOAT、PE Hi"附近使用的正确语法

but i receive this error: MySQL Error [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 ''SALES in millions' FLOAT,'Earnings per share' FLOAT,'PE Ratio TTM' FLOAT,'PE Hi' at line 2

有人看到问题出在哪里了吗?

does anyone see where the problem is?

推荐答案

首先,正如这里所说:检查有效的 SQL 列名

SQL 标识符和关键字必须以字母(a-z,但也可以是带有变音符和非拉丁字母的字母)或下划线 (_) 开头.标识符或关键字中的后续字符可以是字母、下划线、数字 (0-9) 或美元符号 ($).请注意,根据 SQL 标准的字母,标识符中不允许使用美元符号,因此使用它们可能会使应用程序的可移植性降低

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable

它来自 PostGre doc,但因为 PostGre 非常接近理想"的 SQL 语法,它可能与 mysql 相同......所以列名中没有括号,没有空格......

It comes from PostGre doc, but because PostGre is very close to the "ideal" SQL syntax, it might be the same for mysql... So no parenthesis into column names, no spaces...

其次,列名不是字符串:

以下语法有效:

CREATE TABLE (test VARCHAR(100) NOT NULL, ...)

而以下无效,会抛出语法错误:

And the following one is invalid and will throw a syntax error:

CREATE TABLE ('test' VARCHAR(100) NOT NULL, ...)

当您使用%s"修饰符时,它会将数据解析为 STRING.所以它用引号括起来,这是无效的...

When you use the '%s' modifier, it parses data as STRING. so it surrounds it with quotes, which is invalid...

因此,为了创建您的表,我建议使用for 循环"来验证数据(使用 regexpr),并将其添加到字符串中:

So for create your table, I suggest a "for loop" which validate data (with regexpr), and symply add it to the string:

import re
# ...
query = "CREATE TABLE test (ID INT AUTO_INCREMENT,name VARCHAR(50)"
for c in columnames:
        if (re.search(r"^[A-Za-z][A-Za-z0-9_]*$", c) query += c + ", FLOAT" #this regex validate string if  it begins with alphabetic char (upper or lower case), and if the others characters are alphanumeric, or are underscores
        else raise SyntaxError("Invalid Column name!!") #If not, we raise a syntax error
query += ");"

然后你可以创建你的表:)

And then you can create your table :)

这篇关于查询中的python mysql错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆