python连接数据库脚本
代码如下(示例):
# -*- coding: UTF-8 -*-
import MySQLdb
import traceback
import time, logging, json, datetime, csv, cStringIO, codecs, decimal, re
from DBUtils.PooledDB import PooledDB
g_dbIp = '127.0.0.1'
g_user = 'root'
g_password = '123456'
g_dbName = 'XXX'
class CJsonEncoder(json.JSONEncoder):
def default(self, obj):
if isinstance(obj, datetime.datetime):
return obj.strftime('%Y-%m-%d %H:%M:%S')
elif isinstance(obj, datetime.date):
return obj.strftime('%Y-%m-%d')
elif isinstance(obj, decimal.Decimal):
return int(obj)
else:
return json.JSONEncoder.default(self, obj)
g__pool = PooledDB(creator=MySQLdb, mincached=3, maxcached=20, host=g_dbIp, user=g_user, passwd=g_password, maxconnections=20, blocking=True, db=g_dbName, use_unicode=True, charset='utf8')
class MysqlDbo(object):
def write(self, sql):
conn = g__pool.connection()
cursor = conn.cursor()
try:
cursor.execute(sql)
conn.commit()
except:
conn.rollback()
cursor.close()
conn.close()
def read(self, sql):
conn = g__pool.connection()
cursor = conn.cursor()
data = '[]'
try:
rs = cursor.execute(sql)
rows = cursor.fetchall()
data = json.dumps(rows, cls=CJsonEncoder)
except:
print("read fail")
cursor.close()
conn.close()
return data
if __name__ == '__main__':
dbo = MysqlDbo()
a = dbo.read('select protocol,count(*) as count from traffics where protocol in ("ARP","ICMP", "SYN", "PAYLOAD") group by protocol having count>0')