mysql_db.py
2.98 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import configparser
import pymysql.cursors
import os
from . import config_data as Data
#
# base_dir = str(os.path.dirname(os.path.dirname(__file__)))
# config_dir = base_dir.replace('\\', '/')
# config_path = config_dir + '/config.ini'
#
# cp = configparser.ConfigParser()
# cp.read(config_path)
# DB_host = cp.get('mysqlconf', 'host')
# DB_port = cp.get('mysqlconf', 'port')
# DB_username = cp.get('mysqlconf', 'user')
# DB_password = cp.get('mysqlconf', 'password')
# DB_dbName = cp.get('mysqlconf', 'db_name')
class DB:
def __init__ (self):
try:
self.connection = pymysql.connect(host=Data.DB_HOST,
port=Data.DB_PORT,
user=Data.DB_USERNAME,
password=Data.DB_PASSWORD,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
except pymysql.err.OperationalError as e:
print ("MySql error %d: %s" % (e.args[0], e.args[1]))
def select(self, sql):
with self.connection.cursor() as cursor:
if cursor.execute(sql):
return cursor.fetchall()
else:
return None
def select_(self, tabel_name, where_data_equal, where_data_unequal = None ):
sql_where = ' and '.join("{} = '{}' ".format(key, value) for (key, value) in where_data_equal.items())
if where_data_unequal:
sql_where = sql_where + ' and ' + where_data_unequal
sql = 'select count(1) from ' + tabel_name + ' where ' + sql_where
with self.connection.cursor() as cursor:
cursor.execute(sql)
result = cursor.fetchone()
return result['count(1)']
def insert(self, table_name, table_data):
for key in table_data:
table_data[key] = " '" + str(table_data[key]) + "'"
key = ','.join(table_data.keys())
value = ','.join(table_data.values())
real_sql = 'INSERT INTO ' + table_name + " (" + key + " ) VALUES ( "\
+ value + " )"
with self.connection.cursor() as cursor:
cursor.execute(real_sql)
self.connection.commit()
def update(self, table_name, set_data, where_data):
sql_set = ','.join("{}='{}'".format(key, value) for (key, value) in set_data.items())
sql_where = ' and '.join("{}='{}'".format(key, value) for (key, value) in where_data.items())
print(sql_set, sql_where)
real_sql = "UPDATE " + table_name + " SET " + sql_set + " WHERE " + sql_where
with self.connection.cursor() as cursor:
cursor.execute(real_sql)
self.connection.commit()
def update_(self, sql):
with self.connection.cursor() as cursor:
cursor.execute(sql)
self.connection.commit()
def close(self):
self.connection.close()