dhdbnpeM

1import logging
2import MySQLdb
3
4class _MySQL(object):
5     def __init__(self, host, port, user, passwd, db, charset='utf8'):
6        self.conn = MySQLdb.connect(
7                host = host,
8                port = port,
9                user = user,
10                passwd = passwd,
11                db = db,
12                charset = charset)
13
14    def get_cursor(self):
15        return self.conn.cursor()
16
17    def query(self, sql):
18        cursor = self.get_cursor()  
19        try:
20            cursor.execute(sql, None)
21            result = cursor.fetchall()  
22        except Exception, e:
23            logging.error("mysql query error: %s", e)
24            return None
25        finally:
26            cursor.close()
27        return result
28
29    def execute(self, sql, param=None):
30        cursor = self.get_cursor()
31        try:
32            cursor.execute(sql, param)
33            self.conn.commit()
34            affected_row = cursor.rowcount
35        except Exception, e:
36            logging.error("mysql execute error: %s", e)
37            return 0
38        finally:
39            cursor.close()
40        return affected_row
41
42    def executemany(self, sql, params=None):
43        cursor = self.get_cursor()
44        try:
45            cursor.executemany(sql, params)
46            self.conn.commit()
47            affected_rows = cursor.rowcount
48        except Exception, e:
49            logging.error("mysql executemany error: %s", e)
50            return 0
51        finally:
52            cursor.close()
53        return affected_rows
54
55    def close(self):
56        try:
57            self.conn.close()
58        except:
59            pass
60
61    def __del__(self):
62        self.close()
63
64
65host = 'localhost'
66port = 3306
67user = 'root'
68passwd = '123456'
69db = 'foo'
70
71mysql = _MySQL(host, port, user, passwd, db)
72
73def create_table():
74    table = """
75            CREATE TABLE IF NOT EXISTS `watchdog`(
76                `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
77                `name` varchar(100),
78                `price` int(11) NOT NULL DEFAULT 0
79            ) ENGINE=InnoDB charset=utf8;
80            """
81    print mysql.execute(table)
82
83def insert_data():
84    params = [('dog_%d' % i, i) for i in xrange(12)]
85    sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);"   
86    print mysql.executemany(sql, params) 
87
88if __name__ == '__main__':
89    create_table()
90    insert_data()