8
\$\begingroup\$

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')
\$\endgroup\$
1
  • \$\begingroup\$ Can you explain me which args must passed for Select method here ? cheers \$\endgroup\$
    – user97118
    Feb 9, 2016 at 11:00

2 Answers 2

3
\$\begingroup\$

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 ?

\$\endgroup\$
1
  • \$\begingroup\$ Hi thanks for the recomendations. I handle those parameters in this way fisrt i make the query sentence leaving all parameters input with %s, why? because i send all parameter in execute method and it ensures to set all values in the query. \$\endgroup\$ Jan 11, 2014 at 5:20
1
\$\begingroup\$

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

\$\endgroup\$
1
  • \$\begingroup\$ its correct but i think that is the behavior of singleton object, right?, when i did the connection class I did't think that it was used for have two databases but it is a good observation i think that i could include the posibility of have two o more databases in the future. \$\endgroup\$ Feb 21, 2019 at 23:47

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.