def insertTechnicsItem(self, name, model, cost, purcase_date, department,
room):
try:
self.cursor.execute("""
SELECT room_id
FROM company_orgtechnics.Rooms r
INNER JOIN company_orgtechnics.Departments d
ON r.department_id = d.department_id
WHERE d.short_name='{0}' AND r.name='{1}'
""".format(department, room))
room_id = self.cursor.fetchall()
if len(room_id) == 0:
raise mysql.Error()
else:
room_id = room_id[0][0]
self.cursor.execute("""
INSERT INTO company_orgtechnics.Computers
(comp_name,model,cost,purchase_date,room_id)
VALUES('{0}','{1}',{2},'{3}',{4})
""".format(name, model, cost, purcase_date, room_id))
self.db.commit()
except mysql.Error as error:
print "Error %d: %s" % (error.args[0], error.args[1])
self.db.rollback()
raise mysql.Error(error.args)
def get_content_ids(cursor, page_id):
'''get content ids that correspond to revisions for the given page id'''
content_ids = []
revs_query = "SELECT rev_id from revision WHERE rev_page = {page_id};".format(
page_id=page_id)
try:
cursor.execute(revs_query.encode('utf-8'))
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception for getting revs list for page ({errno}:{message})".
format(errno=ex.args[0], message=ex.args[1])) from None
rev_rows = cursor.fetchall()
for rev_row in rev_rows:
if not rev_row:
break
slots_query = "SELECT slot_content_id from slots WHERE slot_revision_id = {rev_id};".format(
rev_id=rev_row[0])
try:
cursor.execute(slots_query.encode('utf-8'))
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception getting revs list for page ({errno}:{message})".
format(errno=ex.args[0], message=ex.args[1])) from None
slot_rows = cursor.fetchall()
for slot_row in slot_rows:
if not slot_row:
break
content_ids.append(slot_row[0])
return content_ids
def get_starting_rev(endrev, db_cursor, verbose):
"""return the starting rev number: if endrev is a number,
return that. if it is the special value 'max', ask the db
for the max rev id and return that"""
if endrev != 'max':
return endrev
# go ask the db
query = 'SELECT MAX(rev_id) from revision;'
try:
if verbose:
print(query)
db_cursor.execute(query.encode('utf-8'))
except MySQLdb.Error as ex:
raise MySQLdb.Error("exception getting max rev id ({errno}:{message})".format(
errno=ex.args[0], message=ex.args[1])) from None
revid_rows = db_cursor.fetchall()
rev_ids = []
for revid_row in revid_rows:
if not revid_row:
break
rev_ids.append(revid_row[0])
if len(rev_ids) != 1:
raise MySQLdb.Error("expected one entry with max rev id, found {count})".format(
count=len(rev_ids))) from None
return int(rev_ids[0])
def createDataStore(self, username, dim):
''' create data store and push the store to the data
server. '''
self.manageUserDataDb()
print 'LOG --- User Data Store Creation request. - ProxyDataManager.'
try:
#self.manageUserDataDb()
conn = self.manage_db.connectDB()
if conn == None: raise MySQLdb.Error('DB connection error! ')
cursor = conn.cursor()
sql = "SELECT * FROM data WHERE user_id = '%s' \
AND data_dim = '%d'" % (username, dim)
cursor.execute(sql)
data = cursor.fetchall()
if len(data) != 0:
return 'Data store already exists, you can upload data to it now.'
else:
try:
db_name = username + '_' + str(dim)
sql = "INSERT INTO data (user_id, data_dim, data_file_name, key_string, \
data_record_cnt) VALUES('%s', '%d', '%s', '%s', '%d')" % \
(username, dim, db_name, '(A,v)', 10)
cursor.execute(sql)
conn.commit()
return 'Successfully create,' + db_name + '!'
except:
conn.commit()
raise MySQLdb.Error(
'Database error while creating data store: ', db_name)
except MySQLdb.Error, e:
print "Error %s: %s" % (e.args[0], e.args[1])
return 'Error happened during registration process...'
def userRegistration(self, username, passwd):
''' Handle registration request from the data user. '''
print 'LOG --- User registration request. - ProxyUserManager.'
try:
conn = self.manage_db.connectDB()
if conn == None: raise MySQLdb.Error('DB connection error! ')
cursor = conn.cursor()
cursor.execute(''' SELECT * FROM user WHERE user_id = %s''',
(username))
data = cursor.fetchall()
if len(data) != 0:
return 'User already exists, please choose a different username.'
else:
try:
cursor.execute(
''' INSERT INTO user (user_id, user_pwd)
VALUES(%s, %s)''', (username, passwd))
conn.commit()
return 'Welcome,' + username + '!'
except:
conn.rollback()
raise MySQLdb.Error(
'Database error while creating user: '******'Error happened during registration process...'
def editTechnicsItem(self, id, name, model, cost, purcase_date, department,
room):
try:
self.cursor.execute("""
SELECT room_id
FROM company_orgtechnics.Rooms r
INNER JOIN company_orgtechnics.Departments d
ON r.department_id = d.department_id
WHERE d.short_name='{0}' AND r.name='{1}'
""".format(department, room))
room_id = self.cursor.fetchall()
if len(room_id) == 0:
raise mysql.Error()
else:
room_id = room_id[0][0]
self.cursor.execute("""
UPDATE company_orgtechnics.Computers
SET comp_name='{1}',model='{2}',cost='{3}',purchase_date='{4}',room_id='{5}'
WHERE comp_id='{0}'
""".format(id, name, model, cost, purcase_date, room_id))
self.db.commit()
except mysql.Error as error:
print "Error %d: %s" % (error.args[0], error.args[1])
self.db.rollback()
raise mysql.Error(error.args)
def run_on_wiki(self, host, wiki, cursor, wiki_settings):
'''
run all queries for a specific wiki, after filling in the
query template; this assumes a db cursor is passed in
'''
print("wiki:", wiki)
queries = self.fillin_query_template(wiki_settings)
self.dbinfo.do_use_wiki(wiki, cursor)
if self.args['dryrun']:
for query in queries:
self.log.info("would run %s", qutils.prettyprint_query(query))
return
for query in queries:
self.log.info("running:")
self.log.info(qutils.prettyprint_query(query))
# be nice to the servers
time.sleep(0.05)
try:
cursor.execute(query.encode('utf-8'))
result = cursor.fetchall()
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception running query on host "
"{host}, wiki {wiki} ({errno}:{message})".format(
host=host, wiki=wiki, errno=ex.args[0], message=ex.args[1]))
print(qutils.prettyprint_query(query))
print(qutils.prettyprint_rows(result, cursor.description))
def close(self):
try:
if self.__connection:
self.__connection.close()
self.__connection = None
except MySQLdb.Error, e:
raise MySQLdb.Error(e)
def async_query(wiki, cursor, query, log):
'''
meant to be run as a thread, execute a query via the specified cursor,
don't bother to return the results, just read and throw them away
this gets shot by the caller, we only care about it running so we
can show explain on its mysql process
'''
try:
cursor.execute(query.encode('utf-8'))
# we don't expect to get through this, we should be killed long before,
# but let's have this here to be nice
row = cursor.fetchone()
while row is not None:
row = cursor.fetchone()
cursor.close()
except MySQLdb.Error as ex:
if ex.args[0] == 2013 or ex.args[0] == 1317:
# this means it has been shot (probably), in any case we don't care
# 1317: Query execution was interrupted
# 2013: Lost connection to MySQL server during query
log.info(
"Async Query: lost connection or query execution interrupted on wiki "
"%s (%s:%s)", wiki, ex.args[0], ex.args[1])
else:
raise MySQLdb.Error(
("Async Query: exception running query on wiki "
"{wiki} ({errno}:{message})".format(wiki=wiki,
errno=ex.args[0],
message=ex.args[1])))
def explain_and_kill(self, host, wiki, thread_id, query):
'''
given the thread id of the thread running
our query, show explain it, then shoot
the query
'''
if self.args['dryrun']:
cursor = None
else:
cursor, _unused = self.dbinfo.get_cursor(host)
explain_result, description = self.explain(wiki, cursor, thread_id)
self.kill(wiki, cursor, thread_id)
qutils.print_and_log(self.log, "*** QUERY:")
qutils.print_and_log(self.log, qutils.prettyprint_query(query))
qutils.print_and_log(self.log, "*** SHOW EXPLAIN RESULTS:")
qutils.print_and_log(
self.log, qutils.prettyprint_rows(explain_result, description))
if cursor is not None:
cursor.close()
# additional insurance.
result = self.check_if_mysqlthr_exists(host, thread_id)
self.log.info("check if query still running: %s", result)
if result is None or result:
# we had a problem checking, or the thread is still there
# and presumably the kill failed
self.log.error(
"quitting while we're behind; run the following on host %s",
host)
self.log.error("echo 'kill {thread_id}' | mysql --skip-ssl")
raise MySQLdb.Error(
"query thread {id} still running".format(id=thread_id))
def explain(self, wiki, cursor, thread_id):
'''
show explain for a given thread id, given an
initialized db cursor
'''
explain_query = 'SHOW EXPLAIN FOR ' + thread_id + ';'
if self.args['dryrun']:
self.log.info("would run %s",
qutils.prettyprint_query(explain_query))
return None, None
self.log.info("running:")
self.log.info(qutils.prettyprint_query(explain_query))
try:
cursor.execute(explain_query.encode('utf-8'))
description = cursor.description
explain_result = cursor.fetchall()
except MySQLdb.Error as ex:
if ex.args[0] == 1933 or ex.args[0] == 1094:
# 1933:Target is not running an EXPLAINable command, i.e. query is already complete
# 1094:Unknown thread id, i.e. query is already complete
explain_result = None
description = None
else:
raise MySQLdb.Error("exception explaining query on wiki "
"{wiki} ({errno}:{message})".format(
wiki=wiki,
errno=ex.args[0],
message=ex.args[1])) from None
return explain_result, description
def insertIntoDB(self, table, dict):
try:
if (self.conn != None):
cursor = self.conn.cursor()
else:
raise MySQLdb.Error('No connection')
sql = "insert into " + table + "("
param = []
for key in dict:
sql += key + ','
param.append(dict.get(key))
param = tuple(param)
sql = sql[:-1] + ") values("
for i in range(len(dict)):
sql += "%s,"
sql = sql[:-1] + ")"
self.logger.debug(sql % param)
n = cursor.execute(sql, param)
self.conn.commit()
cursor.close()
except MySQLdb.Error, e:
self.logger.error("Mysql Error %d: %s" % (e.args[0], e.args[1]))
self.conn.rollback()
def fixup_content_model(dbcursor, model_name):
'''find all pages with content model the specified model_name,
find all the content rows tha correspond to that page's revisions,
and set the content model for those content rows to the model
corresponding to that model name'''
model_ids = []
get_model_id(dbcursor, model_name)
model_rows = dbcursor.fetchall()
for model_row in model_rows:
if not model_row:
break
model_ids.append(model_row[0])
if len(model_ids) != 1:
raise MySQLdb.Error(
"expected one model with name {name} but found {count})".format(
name=model_name, count=len(model_ids))) from None
get_model_page_ids(dbcursor, model_name)
rows = dbcursor.fetchall()
for row in rows:
if not row:
break
# get all the content ids for the revisions for this page
content_ids = get_content_ids(dbcursor, page_id=row[0])
for content_id in content_ids:
do_update_content_row(dbcursor, content_id, model_ids[0],
model_name)
def findByCondition(self, table, fields, wheres):
try:
if (self.conn != None):
cursor = self.conn.cursor()
else:
raise MySQLdb.Error('No connection')
sql = "select "
for field in fields:
sql += field + ","
sql = sql[:-1] + " from " + table + " where "
param = []
values = ''
for where in wheres:
sql += where.key + "='%s' and "
param.append(where.value)
param = tuple(param)
self.logger.debug(sql)
n = cursor.execute(sql[:-5] % param)
self.conn.commit()
cursor.close()
except MySQLdb.Error, e:
self.logger.error("Mysql Error %d: %s" % (e.args[0], e.args[1]))
def get_cursor(self, dbhost, set_domain=False, warn_on_err=False):
'''
split the db host string into hostname and port if necessary,
open a connection, get and return a cursor
'''
if ':' in dbhost:
fields = dbhost.split(':')
host = fields[0]
port = int(fields[1])
else:
host = dbhost
port = 3306
if set_domain and self.args['domain']:
host = host + '.' + self.args['domain']
try:
dbconn = MySQLdb.connect(host=host,
port=port,
user=self.dbcreds['wgDBuser'],
passwd=self.dbcreds['wgDBpassword'])
return dbconn.cursor(), dbconn.thread_id()
except MySQLdb.Error as ex:
if warn_on_err:
self.log.warning(
"failed to connect to or get cursor from %s:%s, %s %s",
host, port, ex.args[0], ex.args[1])
return None, None
raise MySQLdb.Error("failed to connect to or get cursor from "
"{host}:{port}, {errno}:{message}".format(
host=host,
port=port,
errno=ex.args[0],
message=ex.args[1])) from None
def clear(request):
"""
Выполняет TRUNCATE таблиц базы forumDB
"""
if request.method == "GET":
try:
db = connect()
cursor = db.cursor()
cursor.execute("SET FOREIGN_KEY_CHECKS = 0")
cursor.execute("TRUNCATE TABLE Users")
cursor.execute("TRUNCATE TABLE Forums")
cursor.execute("TRUNCATE TABLE Threads")
cursor.execute("TRUNCATE TABLE Posts")
cursor.execute("TRUNCATE TABLE Followers")
cursor.execute("TRUNCATE TABLE Subscriptions")
cursor.execute("SET FOREIGN_KEY_CHECKS = 1")
cursor.close()
db.close()
except MySQLdb.Error:
raise MySQLdb.Error("Ошибка во время TRUNCATE")
return HttpResponse(json.dumps({
"code": 0,
"response": "TRUNCATE is success!"
}),
content_type='application/json')
return HttpResponse(status=400)
def test_mysql_command_internal_error(self, db_cursor_mock):
self.db.cmd_retries = 2
self.db.cmd_retry_wait = 0.1
db_cursor_mock.execute.side_effect = MySQLdb.Error()
self.assertRaises(exceptions.DbError, self.db.mysql_command, 'execute',
'test_sql', True, True, 'test_arg')
db_cursor_mock.execute.assert_called_with('test_sql', ('test_arg', ))
self.assertEqual(db_cursor_mock.execute.call_count, 2)
def upload(self, query):
curs = self.db.cursor()
try:
curs.execute(query)
self.db.commit()
except MySQLdb.Error, e:
raise MySQLdb.Error('Error in upload. %s' % e)
self.db.rollback()
def use_db(cursor, wiki):
use_query = 'USE ' + wiki + ';'
try:
cursor.execute(use_query.encode('utf-8'))
_result = cursor.fetchall()
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception for use {wiki} ({errno}:{message})".format(
wiki=wiki, errno=ex.args[0], message=ex.args[1])) from None
def __init__(self):
try:
self.conn = MySQLdb.connect(host="localhost",
user="******",
passwd="XXXX",
db="net-perf")
self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
except MySQLdb.Error(self.e):
print "DB Error %s %s" % (self.e.args[0], self.e.args[1])
def get_model_id(cursor, model_name):
'''get the content model id for the model name specified, from content model table'''
model_query = "SELECT model_id from content_models WHERE model_name = '{model_name}';".format(
model_name=model_name)
try:
cursor.execute(model_query.encode('utf-8'))
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception for getting model id ({errno}:{message})".format(
errno=ex.args[0], message=ex.args[1])) from None
def get_rev_timestamps(cursor):
'''get rows of rev timestamp and id from revision table, sure hope
we get a generator instead of a ginormous list'''
rev_query = 'SELECT rev_timestamp, rev_id from revision;'
try:
cursor.execute(rev_query.encode('utf-8'))
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception for getting rev timestamp ({errno}:{message})".format(
errno=ex.args[0], message=ex.args[1])) from None
def get_actors(cursor):
'''get rows of actor text and id from actor table, sure hope
we get a generator instead of a ginormous list'''
actor_query = 'SELECT actor_id, actor_name from actor;'
try:
cursor.execute(actor_query.encode('utf-8'))
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception for getting rev users ({errno}:{message})".format(
errno=ex.args[0], message=ex.args[1])) from None
def deleteTechnicsItem(self, id):
try:
self.cursor.execute("""
DELETE FROM company_orgtechnics.Computers
WHERE comp_id='{0}'
""".format(id))
self.db.commit()
except mysql.Error as error:
print "Error %d: %s" % (error.args[0], error.args[1])
self.db.rollback()
raise mysql.Error(error.args)
def cursor(self):
try:
for i in range(0, self.__retry_times + 1):
if self.__connection is None:
self.__connection = self.connection()
connection = self.__connection
try:
cursor = connection.cursor(
cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute("set autocommit=1;")
except MySQLdb.Error, e:
# retry when mysql server has gone away
if e[0] == 2006:
self.close()
continue
else:
raise MySQLdb.Error(e)
return connection, cursor
except MySQLdb.Error, e:
raise MySQLdb.Error(e)
def calgrandtotal():
try:
sql = "select sum(total) from custitems"
cursor.execute(sql)
db.commit()
data = cursor.fetchall()
grandtotal = data[0][0]
print "Grand Total is " , grandtotal
return grandtotal
except MySQLdb.Error(),e:
print e
def execute(self, sql):
try:
if (self.conn != None):
cursor = self.conn.cursor()
else:
raise MySQLdb.Error('No connection')
n = cursor.execute(sql)
return n
except MySQLdb.Error, e:
self.logger.error("Mysql Error %d: %s" % (e.args[0], e.args[1]))
def get_model_page_ids(cursor, model_name):
'''get rows from page table where content model is the model name specified, sure hope
we get a generator instead of a ginormous list'''
page_query = "SELECT page_id from page WHERE page_content_model = '{model_name}';".format(
model_name=model_name)
try:
cursor.execute(page_query.encode('utf-8'))
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception for getting page list ({errno}:{message})".format(
errno=ex.args[0], message=ex.args[1])) from None
def exec_query(query, params):
try:
con = connect_to_db()
with con:
cursor = con.cursor()
cursor.execute(query, params)
result = cursor.fetchall()
cursor.close()
con.close()
except MySQLdb.Error:
raise MySQLdb.Error("Database error in usual query")
return result
def add_report(self, prepared_report):
#print "INSERT INTO report (Species, Postcode,Picture, Comment,NickName) VALUES "+ prepared_report
try:
#print "\n\nPrepared report:" ,prepared_report,"\n\n"
self.Cursor.execute(
"INSERT INTO report (Species, Postcode, Picture, Comment, NickName) VALUES "
+ prepared_report)
self.commit()
#print "\n\nAdd to SQL database successful!\n\n"
except MySQLdb.Error() as Error:
print format(Error)
self.commit()
