Python Mysql 类错误

Question

我正在尝试使用这个sql类https://github.com/nestordeharo/mysql-python-class与 mysql 本地数据库交互(带有 easydevser 的服务器，mysql 版本 MySQL 5.7.10 )，但是当我尝试进行选择时，我总是收到错误消息。

这是 mysql 类:

import MySQLdb, sys
from collections import OrderedDict

class MysqlPython(object):
"""
    Python Class for connecting  with MySQL server and accelerate development project using MySQL
    Extremely easy to learn and use, friendly construction.
"""

__instance   = None
__host       = None
__user       = None
__password   = None
__database   = None
__session    = None
__connection = None

def __new__(cls, *args, **kwargs):
    if not cls.__instance or not cls.__database:
         cls.__instance = super(MysqlPython, cls).__new__(cls,*args,**kwargs)
    return cls.__instance
## End def __new__

def __init__(self, host='localhost', user='root', password='', database=''):
    self.__host     = host
    self.__user     = user
    self.__password = password
    self.__database = database
## End def __init__

def __open(self):
    try:
        cnx = MySQLdb.connect(self.__host, self.__user, self.__password, self.__database)
        self.__connection = cnx
        self.__session    = cnx.cursor()
    except MySQLdb.Error as e:
        print "Error %d: %s" % (e.args[0],e.args[1])
## End def __open

def __close(self):
    self.__session.close()
    self.__connection.close()
## End def __close

def select(self, table, where=None, *args, **kwargs):
    result = None
    query = 'SELECT '
    keys = args
    values = tuple(kwargs.values())
    l = len(keys) - 1

    for i, key in enumerate(keys):
        query += "`"+key+"`"
        if i < l:
            query += ","
    ## End for keys

    query += 'FROM %s' % table

    if where:
        query += " WHERE %s" % where
    print(query)
    ## End if where

    self.__open()
    self.__session.execute(query, values)
    number_rows = self.__session.rowcount
    number_columns = len(self.__session.description)

    if number_rows >= 1 and number_columns > 1:
        result = [item for item in self.__session.fetchall()]
    else:
        result = [item[0] for item in self.__session.fetchall()]
    self.__close()

    return result
## End def select

def update(self, table, where=None, *args, **kwargs):
    query  = "UPDATE %s SET " % table
    keys   = kwargs.keys()
    values = tuple(kwargs.values()) + tuple(args)
    l = len(keys) - 1
    for i, key in enumerate(keys):
        query += "`"+key+"` = %s"
        if i < l:
            query += ","
        ## End if i less than 1
    ## End for keys
    query += " WHERE %s" % where

    self.__open()
    self.__session.execute(query, values)
    self.__connection.commit()

    # Obtain rows affected
    update_rows = self.__session.rowcount
    self.__close()

    return update_rows
## End function update

def insert(self, table, *args, **kwargs):
    values = None
    query = "INSERT INTO %s " % table
    if kwargs:
        keys = kwargs.keys()
        values = tuple(kwargs.values())
        query += "(" + ",".join(["`%s`"] * len(keys)) %  tuple (keys) + ") VALUES (" + ",".join(["%s"]*len(values)) + ")"
    elif args:
        values = args
        query += " VALUES(" + ",".join(["%s"]*len(values)) + ")"

    self.__open()
    self.__session.execute(query, values)
    self.__connection.commit()
    self.__close()
    return self.__session.lastrowid
## End def insert

def delete(self, table, where=None, *args):
    query = "DELETE FROM %s" % table
    if where:
        query += ' WHERE %s' % where

    values = tuple(args)

    self.__open()
    self.__session.execute(query, values)
    self.__connection.commit()

    # Obtain rows affected
    delete_rows = self.__session.rowcount
    self.__close()

    return delete_rows
## End def delete

def select_advanced(self, sql, *args):
    od = OrderedDict(args)
    query  = sql
    values = tuple(od.values())
    self.__open()
    self.__session.execute(query, values)
    number_rows = self.__session.rowcount
    number_columns = len(self.__session.description)

    if number_rows >= 1 and number_columns > 1:
        result = [item for item in self.__session.fetchall()]
    else:
        result = [item[0] for item in self.__session.fetchall()]

    self.__close()
    return result
## End def select_advanced
## End class

这是我创建实例并使用选择方法时的代码部分:

db = MysqlPython(host='localhost', user='root', password='', database='mgdeal')

conditional_query = 'categorie = %s '

result = db.select('table 2', conditional_query, 'nom', 'url', categorie='Lego')

然后我收到以下错误

SELECT `nom`,`url`FROM table 2 WHERE categorie = %s 

  raise errorclass, errorvalue
_mysql_exceptions.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 'table 2 WHERE categorie = 'Lego'' at line 1")

你有什么想法

Answer 1

我在代码上发现了问题，它来自于 select 方法的这一部分。

query += 'FROM %s' % table

必须替换为

query += 'FROM `%s`' % table