Python Connection with MySQL

Question

I want to share something with this community. I did a class connection between Python and MySQL. I hope you can help me with this project and help me do a better class.

Here is the class connection code:

import mysql __author__ = 'Alejandro' import mysql.connector from mysql.connector import errorcode class Mysql(object): __instance = None __host = None __user = None __password = None __database = None __session = None __connection = None def __new__(cls, *args, **kwargs): if not cls.__instance: cls.__instance = super(Mysql, cls).__new__(cls, *args, **kwargs) return cls.__instance def __init__(self, host='localhost', user='root', password='', database=''): self.__host = host self.__user = user self.__password = password self.__database = database #Open connection with database def _open(self): try: cnx = mysql.connector.connect(host=self.__host, user=self.__user, password=self.__password, database=self.__database) self.__connection = cnx self.__session = cnx.cursor() except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print 'Something is wrong with your user name or password' elif err.errno == errorcode.ER_BAD_DB_ERROR: print 'Database does not exists' else: print err def _close(self): self.__session.close() self.__connection.close() def insert(self, table, *args, **kwargs): values = None query = "INSERT INTO %s " % table if kwargs: keys = kwargs.keys() values = 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 def select(self, table, where=None, *args): result = None query = "SELECT " keys = args l = len(keys) - 1 for i, key in enumerate(keys): query += "`"+key+"`" if i < l: query += "," query += " FROM %s" % table if where: query += " WHERE %" % where self._open() self.__session.execute(query) self.__connection.commit() for result in self.__session.stored_results(): result = result.fetchall() self._close() return result def update(self, table, index, **kwargs): query = "UPDATE %s SET" % table keys = kwargs.keys() values = kwargs.values() l = len(keys) - 1 for i, key in enumerate(keys): query += "`"+key+"`=%s" if i < l: query += "," query += " WHERE index=%d" % index self._open() self.__session.execute(query, values) self.__connection.commit() self._close() def delete(self, table, index): query = "DELETE FROM %s WHERE uuid=%d" % (table, index) self._open() self.__session.execute(query) self.__connection.commit() self._close() def call_store_procedure(self, name, *args): result_sp = None self._open() self.__session.callproc(name, args) self.__connection.commit() for result in self.__session.stored_results(): result_sp = result.fetchall() self._close() return result_sp 

Here is how its use looks like:

from Mysql import Mysql connection = Mysql(host='localhost', user='root', password='', database='test') #Assuming that our table have the fields id and name in this order. #we can use this way but the parameter should have the same order that table #connection.insert('table_name',parameters to insert) connection.insert('test',1, 'Alejandro Mora') #in this case the order isn't matter #connection.insert('table_name', field=Value to insert) connection.insert('test',name='Alejandro Mora', id=1) #connection.select('Table', where="conditional(optional)", field to returned) connection.select('test', where="name = 'Alejandro Mora' ") connection.select('test', None,'id','name') #connection.update('Table', id, field=Value to update) connection.update('test', 1, name='Alejandro') #connection.delete('Table', id) connection.delete('test', 1) #connection.call_store_procedure(prodecure name, Values) connection.call_store_procedure('search_users_by_name', 'Alejandro') 

Answer 1

Everything looks quite neat. Here are a few comments.

 query = "SELECT " l = len(keys) - 1 for i, key in enumerate(keys): query += "`"+key+"`" if i < l: query += "," query += " FROM %s" % table 

can be rewritten :

query = "SELECT `" + "`,`".join(keys) + "` FROM " + table 

(I know that string concatenation might not be the best but it's just to join how you could use join to do what you want to do). The same kind of argument would hold for update.

In select and in call_store_procedure, is this :

 for result in self.__session.stored_results(): result = result.fetchall() 

any better than :

 for result in self.__session.stored_results(): result.fetchall() 

?

Also, just some food for thought as I haven't studied the issue in depth : how do you handle parameters that don't need to be in quotes such as numbers ?

Answer 2

An error maybe happen when the number of date base is two or more in your project. Example as:

class Mysql(object): __instance = None __host = None __user = None __password = None __database = None __session = None __connection = None def __new__(cls, *args, **kwargs): if not cls.__instance: cls.__instance = super(Mysql, cls).__new__(cls, *args, **kwargs) return cls.__instance def __init__(self, host='localhost', user='root', password='', database=''): self.__host = host self.__user = user self.__password = password self.__database = database def prin(self): print(self.__host, self.__user, self.__password, self.__database) a = Mysql('192.168.1.12', 'user', 'user1234', 'test') a.prin() # output ('192.168.1.12', 'user', 'user1234', 'test') b = Mysql('192.168.1.132', 'admin', 'admin1234', 'train') b.prin() # output ('192.168.1.132', 'admin', 'admin1234', 'train') a.prin() # output ('192.168.1.132', 'admin', 'admin1234', 'train')