pymysql 封装操作

#!/user/bin/env python

# -*- coding:utf-8 -*-

import pymysql

from pymysql.errimport OperationalError

from operate_configimport ParserDbConfig

class DB():

def __init__(self):

parser = ParserDbConfig()

try:

self.conn = pymysql.connect(

host=parser.get_value('mysqlconf', 'host'),

                user=parser.get_value('mysqlconf', 'user'),

                password=parser.get_value('mysqlconf', 'password'),

                database=parser.get_value('mysqlconf', 'database'),

                port=int(parser.get_value('mysqlconf', 'port'))

)

except OperationalErroras e:

print("mysql errot %d: %d".format(e.args[0], e.args[1]))

#清除表数据

    def clear(self, table):

sql ="truncate table " + table +";"

        with self.conn.cursor()as cursor:

cursor.execute("set foreign_key_checks=0")

cursor.execute(sql)

self.conn.commit()

#查询表数据

    def select_one(self, table):

sql ="select * FROM " + table +";"

        with self.conn.cursor()as cursor:

cursor.execute(sql)

result = cursor.fetchone()

return result

self.conn.close()

#插入数据  data = {'字段':'值'} 字典

    def insert(self, table, data):

for keyin data:

data[key] ="'" +str(data[key]) +"'"

        key =','.join(data.keys())

values =','.join(data.values())

sql ="insert INTO " + table +"(" + key +") values(" + values +");"

        print(sql)

with self.conn.cursor()as cursor:

# sql = "insert INTO emp1 VALUES (%s,%s,%s,%s);"

            cursor.execute(sql, data)

self.conn.commit()

#关闭数据库

    def close(self):

self.conn.close()

if __name__ =='__main__':

db = DB()

# print(db.select_one('emp1'))

# db.insert(('4', 'zhao', 28, 'f'))

# db.insert('emp1',{'id':5, 'name':'liu', 'age':23, 'sex':'f'})

    datas = {'emp1': [{'id':6, 'name':'liu', 'age':23, 'sex':'f'},

                      {'id':7, 'name':'liu', 'age':23, 'sex':'f'}]

}

for table, datain datas.items():

db.clear(table)

for din data:

db.insert(table, d)

db.close()