016tmi0U

1import mysql
2
3
4__author__ = 'Alejandro'
5import mysql.connector
6from mysql.connector import errorcode
7
8class Mysql(object):
9    __instance = None
10
11    __host = None
12    __user = None
13    __password = None
14    __database = None
15
16    __session = None
17    __connection = None
18
19    def __new__(cls, *args, **kwargs):
20        if not cls.__instance:
21            cls.__instance = super(Mysql, cls).__new__(cls, *args, **kwargs)
22        return cls.__instance
23
24    def __init__(self, host='localhost', user='root', password='', database=''):
25        self.__host = host
26        self.__user = user
27        self.__password = password
28        self.__database = database
29
30    #Open connection with database
31    def _open(self):
32        try:
33            cnx = mysql.connector.connect(host=self.__host, user=self.__user, password=self.__password,
34                                          database=self.__database)
35            self.__connection = cnx
36            self.__session = cnx.cursor()
37        except mysql.connector.Error as err:
38            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
39                print 'Something is wrong with your user name or password'
40            elif err.errno == errorcode.ER_BAD_DB_ERROR:
41                print 'Database does not exists'
42            else:
43                print err
44
45    def _close(self):
46        self.__session.close()
47        self.__connection.close()
48
49    def insert(self, table, *args, **kwargs):
50        values = None
51        query = "INSERT INTO %s " % table
52        if kwargs:
53            keys = kwargs.keys()
54            values = kwargs.values()
55            query += "(" + ",".join(["`%s`"]*len(keys)) % tuple(keys) + ") VALUES(" + ",".join(["%s"]*len(values)) + ")"
56        elif args:
57            values = args
58            query += " VALUES(" + ",".join(["%s"]*len(values)) + ")"
59        self._open()
60        self.__session.execute(query, values)
61        self.__connection.commit()
62        self._close()
63        return self.__session.lastrowid
64
65    def select(self, table, where=None, *args):
66        result = None
67        query = "SELECT "
68        keys = args
69        l = len(keys) - 1
70        for i, key in enumerate(keys):
71            query += "`"+key+"`"
72            if i < l:
73                query += ","
74        query += " FROM %s" % table
75        if where:
76            query += " WHERE %" % where
77        self._open()
78        self.__session.execute(query)
79        self.__connection.commit()
80        for result in self.__session.stored_results():
81            result = result.fetchall()
82        self._close()
83        return result
84
85    def update(self, table, index, **kwargs):
86        query = "UPDATE %s SET" % table
87        keys = kwargs.keys()
88        values = kwargs.values()
89        l = len(keys) - 1
90        for i, key in enumerate(keys):
91            query += "`"+key+"`=%s"
92            if i < l:
93                query += ","
94        query += " WHERE index=%d" % index
95        self._open()
96        self.__session.execute(query, values)
97        self.__connection.commit()
98        self._close()
99
100    def delete(self, table, index):
101        query = "DELETE FROM %s WHERE uuid=%d" % (table, index)
102        self._open()
103        self.__session.execute(query)
104        self.__connection.commit()
105        self._close()
106
107    def call_store_procedure(self, name, *args):
108        result_sp = None
109        self._open()
110        self.__session.callproc(name, args)
111        self.__connection.commit()
112        for result in self.__session.stored_results():
113            result_sp = result.fetchall()
114        self._close()
115        return result_sp 
116     
117from Mysql import Mysql
118connection = Mysql(host='localhost', user='root', password='', database='test')
119#Assuming that our table have the fields id and name in this order.
120#we can use this way but the parameter should have the same order that table
121#connection.insert('table_name',parameters to insert)
122connection.insert('test',1, 'Alejandro Mora')
123#in this case the order isn't matter
124#connection.insert('table_name', field=Value to insert)
125connection.insert('test',name='Alejandro Mora', id=1)
126#connection.select('Table', where="conditional(optional)", field to returned)
127connection.select('test', where="name = 'Alejandro Mora' ")
128connection.select('test', None,'id','name')
129#connection.update('Table', id, field=Value to update)
130connection.update('test', 1, name='Alejandro')
131#connection.delete('Table', id)
132connection.delete('test', 1)
133#connection.call_store_procedure(prodecure name, Values)
134connection.call_store_procedure('search_users_by_name', 'Alejandro') 
135     
136query = "SELECT "
137    l = len(keys) - 1
138    for i, key in enumerate(keys):
139        query += "`"+key+"`"
140        if i < l:
141            query += ","
142    query += " FROM %s" % table 
143     
144query =  "SELECT `" + "`,`".join(keys) + "` FROM " + table 
145     
146for result in self.__session.stored_results():
147        result = result.fetchall() 
148     
149for result in self.__session.stored_results():
150        result.fetchall() 
151     
152class Mysql(object):
153    __instance = None
154
155    __host = None
156    __user = None
157    __password = None
158    __database = None
159
160    __session = None
161    __connection = None
162
163    def __new__(cls, *args, **kwargs):
164        if not cls.__instance:
165            cls.__instance = super(Mysql, cls).__new__(cls, *args, **kwargs)
166        return cls.__instance
167
168    def __init__(self, host='localhost', user='root', password='', database=''):
169        self.__host = host
170        self.__user = user
171        self.__password = password
172        self.__database = database
173
174    def prin(self):
175        print(self.__host, self.__user, self.__password, self.__database)
176
177a = Mysql('192.168.1.12', 'user', 'user1234', 'test')
178a.prin()  # output ('192.168.1.12', 'user', 'user1234', 'test')
179b = Mysql('192.168.1.132', 'admin', 'admin1234', 'train')
180b.prin() # output ('192.168.1.132', 'admin', 'admin1234', 'train')
181a.prin() # output ('192.168.1.132', 'admin', 'admin1234', 'train')
182```