def insert_profile_item(cls, item):
sql = "INSERT INTO `py_review_profile`" \
"(`asin`, `product`, `brand`, `seller`, `image`," \
"`review_total`, `review_rate`, `pct_five`, `pct_four`, `pct_three`, " \
"`pct_two`, `pct_one`, `latest_total`) " \
"VALUES ('%s', %s, %s, %s, '%s', '%s', " \
"'%s', '%s', '%s', '%s', '%s', '%s', 0)" %\
(item['asin'], cls.conn.escape(item['product']), cls.conn.escape(item['brand']), cls.conn.escape(item['seller']), item['image'],
item['review_total'], item['review_rate'], item['pct_five'], item['pct_four'],
item['pct_three'], item['pct_two'], item['pct_one'])
try:
if cls.check_exist_profile(item['asin']):
cls.update_profile_item(item)
print('update review profile--[asin]:', item['asin'])
else:
cls.cursor.execute(sql)
cls.conn.commit()
print('save review profile--[asin]:', item['asin'])
except pymysql.MySQLError as e:
with open('sql.log', 'r+') as i:
i.write('profile sql error![error]:'+e)
print(e)
cls.conn.rollback()
pass
python类MySQLError()的实例源码
def insert_profile_item(cls, item):
sql = "INSERT INTO `py_review_profile`" \
"(`asin`, `product`, `brand`, `seller`, `image`," \
"`review_total`, `review_rate`, `pct_five`, `pct_four`, `pct_three`, " \
"`pct_two`, `pct_one`, `latest_total`) " \
"VALUES ('%s', %s, %s, %s, '%s', '%s', " \
"'%s', '%s', '%s', '%s', '%s', '%s', 0)" %\
(item['asin'], cls.conn.escape(item['product']), cls.conn.escape(item['brand']), cls.conn.escape(item['seller']), item['image'],
item['review_total'], item['review_rate'], item['pct_five'], item['pct_four'],
item['pct_three'], item['pct_two'], item['pct_one'])
try:
if cls.check_exist_profile(item['asin']):
cls.update_profile_item(item)
print('update review profile--[asin]:', item['asin'])
else:
cls.cursor.execute(sql)
cls.conn.commit()
print('save review profile--[asin]:', item['asin'])
except pymysql.MySQLError as e:
with open('sql.log', 'r+') as i:
i.write('profile sql error![error]:'+e)
print(e)
cls.conn.rollback()
pass
def set_desync(cfg, ip_address, port):
"""Set server to desync state."""
try:
server_set_wsrep_desync(cfg, ip_address, port, wsrep_desync='ON')
except NotImplementedError as err:
LOG.error(err)
exit(1)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except ProxySQLBackendNotFound as err:
LOG.error(err)
def set_sync(cfg, ip_address, port):
"""Set server to sync state."""
try:
server_set_wsrep_desync(cfg, ip_address, port, wsrep_desync='OFF')
except NotImplementedError as err:
LOG.error(err)
exit(1)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except ProxySQLBackendNotFound as err:
LOG.error(err)
def init_data_from_database(self, *args, database='mysql', sql=None):
"""
?????????
:param args: host,user,passwd,db
:param database: ???????
:param sql: sql??
:return: ??
"""
def mysql():
"""MySQL???"""
if self.__mysql is None:
if len(args) > 1:
'''????'''
self.__mysql = pymysql.connect(*args)
elif len(args) == 1:
'''????'''
self.__mysql = pymysql.connect(**args[0])
else:
sys.stderr.write('Error: ????')
if sql:
cursor = self.__mysql.cursor()
try:
cursor.execute(sql)
self.__mysql.commit()
data = []
for d in cursor.fetchall():
data.append(d)
cursor.close()
except pymysql.MySQLError:
self.__mysql.rollback()
return data
def sqlserver():
pass
if database is 'mysql':
return mysql()
elif database is 'sqlserver':
return sql()
def update_profile_item(cls, item):
sql = "UPDATE `py_review_profile` SET `product`=%s, `brand`=%s, `seller`=%s, `image`=%s, `review_total`='%s', `review_rate`='%s'," \
"`pct_five`='%s', `pct_four`='%s', `pct_three`='%s', `pct_two`='%s', `pct_one`='%s', `latest_total`=`review_total` " \
"WHERE `asin`='%s'" % \
(cls.conn.escape(item['product']), cls.conn.escape(item['brand']), cls.conn.escape(item['seller']), item['image'],
item['review_total'], item['review_rate'],item['pct_five'], item['pct_four'], item['pct_three'], item['pct_two'],
item['pct_one'], item['asin'])
try:
cls.cursor.execute(sql)
cls.conn.commit()
except pymysql.MySQLError as e:
print(e)
cls.conn.rollback()
def insert_detail_item(cls, item):
sql = "INSERT INTO `py_review_detail`(`asin`, `review_id`, `reviewer`, `review_url`, `star`, `date`, `title`, `content`) " \
"VALUES ('%s', '%s', %s, '%s', '%s', '%s', %s, %s)" % \
(item['asin'], item['review_id'], cls.conn.escape(item['reviewer']), item['review_url'], item['star'],
item['date'], cls.conn.escape(item['title']), cls.conn.escape(item['content']))
try:
if cls.check_exist_detail(item['asin'], item['review_id']) is not True:
print('save review detail--[asin]:', item['asin'], '[reviewID]:', item['review_id'])
cls.cursor.execute(sql)
cls.conn.commit()
except pymysql.MySQLError as e:
print(e)
cls.conn.rollback()
pass
def update_profile_item(cls, item):
sql = "UPDATE `py_review_profile` SET `latest_total`=`review_total`,`product`=%s, `brand`=%s, `seller`=%s, `image`=%s, `review_total`='%s', `review_rate`='%s'," \
"`pct_five`='%s', `pct_four`='%s', `pct_three`='%s', `pct_two`='%s', `pct_one`='%s' " \
"WHERE `asin`='%s'" % \
(cls.conn.escape(item['product']), cls.conn.escape(item['brand']), cls.conn.escape(item['seller']), cls.conn.escape(item['image']),
item['review_total'], item['review_rate'],item['pct_five'], item['pct_four'], item['pct_three'], item['pct_two'],
item['pct_one'], item['asin'])
try:
cls.cursor.execute(sql)
cls.conn.commit()
except pymysql.MySQLError as e:
print(e)
cls.conn.rollback()
def insert_detail_item(cls, item):
sql = "INSERT INTO `py_review_detail`(`asin`, `review_id`, `reviewer`, `review_url`, `star`, `date`, `title`, `content`) " \
"VALUES ('%s', '%s', %s, '%s', '%s', '%s', %s, %s)" % \
(item['asin'], item['review_id'], cls.conn.escape(item['reviewer']), item['review_url'], item['star'],
item['date'], cls.conn.escape(item['title']), cls.conn.escape(item['content']))
try:
if cls.check_exist_detail(item['asin'], item['review_id']) is not True:
print('save review detail--[asin]:', item['asin'], '[reviewID]:', item['review_id'])
cls.cursor.execute(sql)
cls.conn.commit()
except pymysql.MySQLError as e:
print(e)
cls.conn.rollback()
pass
def register(cfg):
"""Registers Galera cluster nodes with ProxySQL."""
try:
galera_register(cfg)
except NotImplementedError as err:
LOG.error(err)
exit(1)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
def status(cfg):
"""Show status of MySQL backends."""
try:
server_status(cfg)
except NotImplementedError as err:
LOG.error(err)
exit(1)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
def create(cfg, username, password, active, use_ssl, # pylint: disable=too-many-arguments
default_hostgroup, default_schema, schema_locked,
transaction_persistent, fast_forward,
backend, frontend, max_connections):
"""Add user of MySQL backend to ProxySQL"""
kwargs = {
'username': username,
'password': password,
'use_ssl': use_ssl,
'active': active,
'default_hostgroup': default_hostgroup,
'default_schema': default_schema,
'schema_locked': schema_locked,
'transaction_persistent': transaction_persistent,
'backend': backend,
'frontend': frontend,
'fast_forward': fast_forward,
'max_connections': max_connections
}
try:
create_user(cfg, kwargs)
LOG.info('User %s successfully created', username)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
def delete(cfg, username):
"""Delete MySQL backend user by username"""
try:
delete_user(cfg, username)
LOG.info('User %s has deleted', username)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except (NoOptionError, NoSectionError) as err:
LOG.error('Failed to parse config: %s', err)
exit(1)
def modify(ctx, cfg, username):
"""Modify MySQL backend user by username"""
try:
modify_user(cfg, username, ctx.args)
LOG.info("User %s has modified", username)
except ProxySQLUserNotFound:
LOG.error("User not found")
exit(1)
except MySQLError as err:
LOG.error('Failed to talk to database: %s', err)
except ValueError:
LOG.error("Invalid input")
exit(1)
