0

I'm trying to use this sql class https://github.com/nestordeharo/mysql-python-class to interact with mysql Local database ( server with easydevser, mysql version MySQL 5.7.10 ),but i'm always get an error msg when i try to make a select.

Here is the mysql class:

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

Here is the part of my code when i made the instance and use a select method:

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

conditional_query = 'categorie = %s '

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

Then i get the followings error

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")

Do you have any idea

2
  • What is the output of print( query ) ??? Add it to your post above. Commented Feb 9, 2016 at 13:04
  • Sorry my mistake i forget to add it. It's my add print to check the query actually Commented Feb 9, 2016 at 13:10

1 Answer 1

1

I found the issue on the code, It's coming from this part for the select method.

query += 'FROM %s' % table

must be replace by

query += 'FROM `%s`' % table
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.