Python操作MySQL数据库-蒲公英云

Python操作MySQL数据库

Bertha 。 2022-08-22 14:20 334阅读 0赞

dbaopers.py

#!/bin/env python
#coding:utf-8
import MySQLdb
class DBAOpers(object):
    def get_conn(self, host='192.168.56.159', user="root", passwd='root', port=3306, autocommit = True):
        conn = None
        try:
            conn=MySQLdb.connect(host, user, passwd, port=port,connect_timeout=3,charset='utf8')
            conn.autocommit(autocommit)
        except Exception,e:
            print e
        return conn
    def query_sql(self, conn, sql, db_name='test'):
        conn.select_db(db_name)
        cursor = conn.cursor()
        try :
            cursor.execute(sql)
            rows = cursor.fetchall()
        except Exception, e :
            print e
        finally:
            cursor.close()
            conn.close()
        return rows
    def dml_sql(self,conn,sql,param='',db_name='test'):
        conn.select_db(db_name)
        cursor = conn.cursor()
        try:
            if param:
                cursor.executemany(sql,param)
            else:
                cursor.execute(sql)
        except Exception,e:
            print e
        finally:
            cursor.close()
            conn.commit()
            conn.close()
db = DBAOpers()

操作事例

#coding: utf-8
import MySQLdb
from dbaopers import db
#一次插入多条数据
insert_many_sql = "insert into user values(%s,%s,%s)"
param = [[4,'qq','q'],[5,'ww','ww'],[6,'qwe','qwe']]
#一次插入一条数据
insert_one_sql = "insert into user values(1,'fyl','hello')"
#查询sql
query_sql = "select * from user;"
db.dml_sql(db.get_conn(), insert_many_sql, param)
db.dml_sql(db.get_conn(),insert_one_sql)
result = db.query_sql(db.get_conn(), query_sql,db_name='test')  
for i in result:
    print i