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