python 2.7 读取mysql_python2.7 and python3 操作MySQL数据库类

python3 mysql.py

pip3 install pymysql

mysql.py

# -*- coding: utf8 -*-

import logging

import pymysql

import pymysql.cursors

from scrapy.utils.project import get_project_settings

settings = get_project_settings()

dbconf = settings['MYSQL_DB']

class _MySQL(object):

def __init__(self, host, port, user, passwd, db, charset='utf8'):

self.conn = pymysql.connect(

host = host,

port = port,

user = user,

passwd = passwd,

db = db,

charset = charset,

cursorclass = pymysql.cursors.DictCursor

)

def get_cursor(self):

return self.conn.cursor()

def query(self, sql):

cursor = self.get_cursor()

try:

cursor.execute(sql, None)

result = cursor.fetchall()

except Exception as e:

logging.error("mysql query error: %s", e)

return None

finally:

cursor.close()

return result

def execute(self, sql, param=None):

cursor = self.get_cursor()

try:

cursor.execute(sql, param)

self.conn.commit()

affected_row = cursor.rowcount

except Exception as e:

logging.error("mysql execute error: %s", e)

return 0

finally:

cursor.close()

return affected_row

def insert(self, sql, param=None):

cursor = self.get_cursor()

try:

cursor.execute(sql, param)

self.conn.commit()

lastrowid = cursor.lastrowid

except Exception as e:

logging.error("mysql insert error: %s", e)

return 0

finally:

cursor.close()

return lastrowid

def executemany(self, sql, params=None):

cursor = self.get_cursor()

try:

cursor.executemany(sql, params)

self.conn.commit()

affected_rows = cursor.rowcount

except Exception as e:

logging.error("mysql executemany error: %s", e)

return 0

finally:

cursor.close()

return affected_rows

def close(self):

try:

self.conn.close()

except:

pass

def __del__(self):

self.close()

mysql = _MySQL(

host=dbconf['host'],

user=dbconf['user'],

passwd=dbconf['password'],

db=dbconf['name'],

port=dbconf['port'],

charset=dbconf['charset'],

)

def create_table():

table = """

CREATE TABLE IF NOT EXISTS `watchdog`(

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` varchar(100),

`price` int(11) NOT NULL DEFAULT 0

) ENGINE=InnoDB charset=utf8;

"""

print(mysql.execute(table))

def insert_data():

params = [('dog_%d' % i, i) for i in range(12)]

sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);"

print(mysql.executemany(sql, params))

def insert():

params = [('dog_%d' % i, i) for i in range(12)]

sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES('%s','%s'), ('%s','%s');" % ('name_test', '100', 'name_tes2', '101')

res = mysql.insert(sql)

print(res)

def select_data():

query = "select `id`,`name`,`price` from `watchdog`;";

li = mysql.query(query)

print(li)

if __name__ == '__main__':

# create_table()

# insert()

# insert_data()

# select_data()

pass

python2.7 mysql.py

pip2 install MySQL-python

mysql.py

# -*- coding: utf8 -*-

import logging

import MySQLdb

import MySQLdb.cursors

from DBConf import db_host

from DBConf import db_port

from DBConf import db_user

from DBConf import db_passwd

from DBConf import db_name

class _MySQL(object):

def __init__(self, host, port, user, passwd, db, charset='utf8'):

self.conn = MySQLdb.connect(

host = host,

port = port,

user = user,

passwd = passwd,

db = db,

charset = charset,

cursorclass = MySQLdb.cursors.DictCursor

)

def get_cursor(self):

return self.conn.cursor()

def query(self, sql):

cursor = self.get_cursor()

try:

cursor.execute(sql, None)

result = cursor.fetchall()

except Exception, e:

logging.error("mysql query error: %s", e)

return None

finally:

cursor.close()

return result

def execute(self, sql, param=None):

cursor = self.get_cursor()

try:

cursor.execute(sql, param)

self.conn.commit()

affected_row = cursor.rowcount

except Exception, e:

logging.error("mysql execute error: %s", e)

return 0

finally:

cursor.close()

return affected_row

def executemany(self, sql, params=None):

cursor = self.get_cursor()

try:

cursor.executemany(sql, params)

self.conn.commit()

affected_rows = cursor.rowcount

except Exception, e:

logging.error("mysql executemany error: %s", e)

return 0

finally:

cursor.close()

return affected_rows

def close(self):

try:

self.conn.close()

except:

pass

def __del__(self):

self.close()

mysql = _MySQL(db_host, db_port, db_user, db_passwd, db_name)

def create_table():

table = """

CREATE TABLE IF NOT EXISTS `watchdog`(

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` varchar(100),

`price` int(11) NOT NULL DEFAULT 0

) ENGINE=InnoDB charset=utf8;

"""

print mysql.execute(table)

def insert_data():

params = [('dog_%d' % i, i) for i in xrange(12)]

sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);"

print mysql.executemany(sql, params)

if __name__ == '__main__':

# create_table()

# insert_data()

pass