Gfrktyl

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

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 ?

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

```