def registerUser(self, uid, cid, uname):
sql = "INSERT INTO `users` (uid, cid, uname) VALUES (%s,%s,%s)"
self.lock.acquire()
cur = self.conn.cursor()
res = 0
try:
cur.execute(sql, (
uid,
cid,
uname,
))
except MySQLdb.IntegrityError:
res = 1
except Exception as e:
print(e, datetime.datetime.now())
traceback.print_exc()
res = 2
self.conn.commit()
cur.close()
self.lock.release()
return res
python类IntegrityError()的实例源码
def addTopic(self, cid, topic):
if not self.rdr.validTopic(topic):
topic = self.rdr.closest(topic)
if topic == None:
return 2
sql = "INSERT INTO `topics` (cid, topic) VALUES (%s, %s)"
self.lock.acquire()
cur = self.conn.cursor()
res = 0
try:
cur.execute(sql, (
cid,
topic,
))
except MySQLdb.IntegrityError:
res = 1
except Exception as e:
print(e, datetime.datetime.now())
traceback.print_exc()
res = 3
self.conn.commit()
cur.close()
self.lock.release()
return res
def insert_row(table, insert):
"""
Execute an ``INSERT`` on a table.
:param basestring table: table name
:param dict insert: inserted data, with keys as the column name and
values as the corresponding values
:returns: the id of the inserted row
:rtype: int or long
:raises AlreadyExists: if the row already exists and insertion would
result in duplicate keys
"""
try:
keys = _encode_name(insert.keys())
values = ','.join([_encode(value) for value in insert.values()])
return _execute("INSERT INTO %s (%s) VALUES (%s);"
% (_encode_name(table), keys, values),
write=True, ret='lastrowid')
except MySQLdb.IntegrityError as e:
if e[0] == 1062:
raise AlreadyExists
raise
def process_item(self, item, spider):
if len(item['ip_port']):
a = Proxy(
ip_port=item['ip_port'],
type=item['type'],
level=item['level'],
location=item['location'],
speed=item['speed'],
lifetime=item['lifetime'],
lastcheck=item['lastcheck'],
rule_id=item['rule_id'],
source=item['source']
)
session = loadSession()
try:
session.merge(a)
session.commit()
except MySQLdb.IntegrityError, e:
log.msg("MySQL Error: %s" % str(e), _level=logging.WARNING)
return item
else:
log.msg("ip_port is invalid!",_level=logging.WARNING)
def process_item(self, item, spider):
conn = MySQLdb.connect(host='localhost', user='root', passwd='wuchujie', charset='utf8', db='cl1024')
cur = conn.cursor()
sql = 'insert into cl1024_torrent(cl_name, cl_url, cl_bankuai, posted, torrent_url, torrent_downloaded, torrent_download_urls) values(%s, %s, %s, %s, %s, %s, %s)'
keys = ['cl_title', 'cl_url', 'cl_bankuai', 'posted', 'torrent_url', 'torrent_downloaded', 'torrent_download_urls']
# for i in item.keys():
# try:
# item[i] = str(item.get(i).encode('utf-8'))
# except:
# item[i] = str(item.get(i))
# finally:
# pass
values = [item.get(x) for x in keys]
try:
cur.execute(sql, values)
except MySQLdb.IntegrityError:
pass
conn.commit()
cur.close()
conn.close()
return item
def select_group_ids(self, resource_name, timestamp):
"""Select the group ids from a snapshot table.
Args:
resource_name (str): String of the resource name.
timestamp (str): String of timestamp, formatted as
YYYYMMDDTHHMMSSZ.
Returns:
list: A list of group ids.
Raises:
MySQLError: When an error has occured while executing the query.
"""
try:
group_ids_sql = select_data.GROUP_IDS.format(timestamp)
cursor = self.conn.cursor(cursorclass=cursors.DictCursor)
cursor.execute(group_ids_sql)
rows = cursor.fetchall()
return [row['group_id'] for row in rows]
except (DataError, IntegrityError, InternalError, NotSupportedError,
OperationalError, ProgrammingError) as e:
raise MySQLError(resource_name, e)
def execute_sql_with_fetch(self, resource_name, sql, values):
"""Executes a provided sql statement with fetch.
Args:
resource_name (str): String of the resource name.
sql (str): String of the sql statement.
values (tuple): Tuple of string for sql placeholder values.
Returns:
list: A list of dict representing rows of sql query result.
Raises:
MySQLError: When an error has occured while executing the query.
"""
try:
cursor = self.conn.cursor(cursorclass=cursors.DictCursor)
cursor.execute(sql, values)
return cursor.fetchall()
except (DataError, IntegrityError, InternalError, NotSupportedError,
OperationalError, ProgrammingError) as e:
raise MySQLError(resource_name, e)
def execute_sql_with_commit(self, resource_name, sql, values):
"""Executes a provided sql statement with commit.
Args:
resource_name (str): String of the resource name.
sql (str): String of the sql statement.
values (tuple): Tuple of string for sql placeholder values.
Raises:
MySQLError: When an error has occured while executing the query.
"""
try:
cursor = self.conn.cursor()
cursor.execute(sql, values)
self.conn.commit()
except (DataError, IntegrityError, InternalError, NotSupportedError,
OperationalError, ProgrammingError) as e:
raise MySQLError(resource_name, e)
def insert(self, data, hostnames):
"""
Insert a job record
"""
cur = self.con.cursor()
try:
query = "INSERT INTO job (resource_id, local_job_id, start_time_ts, end_time_ts, record) VALUES(%s,%s,%s,%s,COMPRESS(%s))"
cur.execute(query, data)
for host in hostnames:
if host not in self._hostlistcache:
cur.execute("INSERT IGNORE INTO hosts (hostname) VALUES (%s)", [host])
self.con.commit()
self._hostlistcache[host] = 1
cur.execute("INSERT INTO jobhosts (jobid, hostid) VALUES( (SELECT id FROM job WHERE resource_id = %s AND local_job_id = %s AND end_time_ts = %s), (SELECT id FROM hosts WHERE hostname = %s) )",
[data[0], data[1], data[3], host])
cur.execute("INSERT INTO process (jobid, ingest_version) VALUES ( (SELECT id FROM job WHERE resource_id = %s AND local_job_id = %s AND end_time_ts = %s), %s)", [data[0], data[1], data[3], INGEST_VERSION])
except mdb.IntegrityError as e:
if e[0] != 1062:
raise e
# else:
# Todo - check that the blobs match on duplicate records
self.buffered += 1
if self.buffered > 100:
self.con.commit()
self.buffered = 0
def insert(self, resource_id, hostname, filename, start, end, jobid):
"""
Insert a job record
"""
cur = self.con.cursor()
try:
if hostname not in self._hostnamecache:
cur.execute("INSERT IGNORE INTO hosts (hostname) VALUES (%s)", [hostname])
self.con.commit()
self._hostnamecache[hostname] = 1
query = """INSERT INTO archive (hostid, filename, start_time_ts, end_time_ts, jobid)
VALUES( (SELECT id FROM hosts WHERE hostname = %s),%s,%s,%s,%s)
ON DUPLICATE KEY UPDATE start_time_ts=%s, end_time_ts=%s"""
cur.execute(query, [hostname, filename, start, end, jobid, start, end])
except mdb.IntegrityError as e:
if e[0] != 1062:
raise e
# else:
# Todo - check that the blobs match on duplicate records
self.buffered += 1
if self.buffered > 100:
self.con.commit()
self.buffered = 0
def execute(self, query, args=None):
try:
# args is None means no string interpolation
return self.cursor.execute(query, args)
except Database.OperationalError as e:
# Map some error codes to IntegrityError, since they seem to be
# misclassified and Django would prefer the more logical place.
if e.args[0] in self.codes_for_integrityerror:
six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
raise
def executemany(self, query, args):
try:
return self.cursor.executemany(query, args)
except Database.OperationalError as e:
# Map some error codes to IntegrityError, since they seem to be
# misclassified and Django would prefer the more logical place.
if e.args[0] in self.codes_for_integrityerror:
six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
raise
def check_constraints(self, table_names=None):
"""
Checks each table name in `table_names` for rows with invalid foreign
key references. This method is intended to be used in conjunction with
`disable_constraint_checking()` and `enable_constraint_checking()`, to
determine if rows with invalid references were entered while constraint
checks were off.
Raises an IntegrityError on the first invalid foreign key reference
encountered (if any) and provides detailed information about the
invalid reference in the error message.
Backends can override this method if they can more directly apply
constraint checking (e.g. via "SET CONSTRAINTS ALL IMMEDIATE")
"""
cursor = self.cursor()
if table_names is None:
table_names = self.introspection.table_names(cursor)
for table_name in table_names:
primary_key_column_name = self.introspection.get_primary_key_column(cursor, table_name)
if not primary_key_column_name:
continue
key_columns = self.introspection.get_key_columns(cursor, table_name)
for column_name, referenced_table_name, referenced_column_name in key_columns:
cursor.execute("""
SELECT REFERRING.`%s`, REFERRING.`%s` FROM `%s` as REFERRING
LEFT JOIN `%s` as REFERRED
ON (REFERRING.`%s` = REFERRED.`%s`)
WHERE REFERRING.`%s` IS NOT NULL AND REFERRED.`%s` IS NULL"""
% (primary_key_column_name, column_name, table_name, referenced_table_name,
column_name, referenced_column_name, column_name, referenced_column_name))
for bad_row in cursor.fetchall():
raise utils.IntegrityError("The row in table '%s' with primary key '%s' has an invalid "
"foreign key: %s.%s contains a value '%s' that does not have a corresponding value in %s.%s."
% (table_name, bad_row[0],
table_name, column_name, bad_row[1],
referenced_table_name, referenced_column_name))
def insertMonData(mondata):
try:
data = json.loads(mondata)
timeOfData = int(data['Time'])
hostIndex = monTables[fnvhash(data['Host']) % len(monTables)]
sql = "insert into `%s` (`host`,`mem_free`,`mem_usage`,`mem_total`,`load_avg`,`time`) VALUES('%s', '%d', '%d', '%d', '%s', '%d')" % \
(hostIndex, data['Host'], data['MemFree'], data['MemUsage'], data['MemTotal'], data['LoadAvg'], timeOfData)
ret = cur.execute(sql)
except mysql.IntegrityError:
pass
def delete(cls, id):
sql = 'delete from movie_order where id=%s'
try:
store.execute(sql, id)
store.commit()
except IntegrityError:
store.rollback()
return False
cls.clear_mc(id)
return True
def delete(cls, id):
sql = 'delete from movie_order where id=%s'
try:
store.execute(sql, id)
store.commit()
except IntegrityError:
store.rollback()
return False
return True
def execute(self, query, args=None):
try:
# args is None means no string interpolation
return self.cursor.execute(query, args)
except Database.OperationalError as e:
# Map some error codes to IntegrityError, since they seem to be
# misclassified and Django would prefer the more logical place.
if e.args[0] in self.codes_for_integrityerror:
six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
raise
def executemany(self, query, args):
try:
return self.cursor.executemany(query, args)
except Database.OperationalError as e:
# Map some error codes to IntegrityError, since they seem to be
# misclassified and Django would prefer the more logical place.
if e.args[0] in self.codes_for_integrityerror:
six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
raise
def check_constraints(self, table_names=None):
"""
Checks each table name in `table_names` for rows with invalid foreign
key references. This method is intended to be used in conjunction with
`disable_constraint_checking()` and `enable_constraint_checking()`, to
determine if rows with invalid references were entered while constraint
checks were off.
Raises an IntegrityError on the first invalid foreign key reference
encountered (if any) and provides detailed information about the
invalid reference in the error message.
Backends can override this method if they can more directly apply
constraint checking (e.g. via "SET CONSTRAINTS ALL IMMEDIATE")
"""
cursor = self.cursor()
if table_names is None:
table_names = self.introspection.table_names(cursor)
for table_name in table_names:
primary_key_column_name = self.introspection.get_primary_key_column(cursor, table_name)
if not primary_key_column_name:
continue
key_columns = self.introspection.get_key_columns(cursor, table_name)
for column_name, referenced_table_name, referenced_column_name in key_columns:
cursor.execute("""
SELECT REFERRING.`%s`, REFERRING.`%s` FROM `%s` as REFERRING
LEFT JOIN `%s` as REFERRED
ON (REFERRING.`%s` = REFERRED.`%s`)
WHERE REFERRING.`%s` IS NOT NULL AND REFERRED.`%s` IS NULL"""
% (primary_key_column_name, column_name, table_name, referenced_table_name,
column_name, referenced_column_name, column_name, referenced_column_name))
for bad_row in cursor.fetchall():
raise utils.IntegrityError("The row in table '%s' with primary key '%s' has an invalid "
"foreign key: %s.%s contains a value '%s' that does not have a corresponding value in %s.%s."
% (table_name, bad_row[0],
table_name, column_name, bad_row[1],
referenced_table_name, referenced_column_name))
def checkValid(item):
starttime = datetime.datetime.now()
rst = checkProxy(proxyIP=item.ip_port,protocol="http",timeout=5)
costtimie = (datetime.datetime.now()-starttime).seconds
if rst is not None and rst["status"] == "ok":
proxy = freshProxy(ip_port=item.ip_port,
type=item.type,
location=rst["rstLocation"].encode("utf-8"),
speed=costtimie,
source=item.source,
rule_id=item.rule_id,
lastcheck=datetime.datetime.now()
)
print rst["rstIP"]
print rst["rstLocation"].encode("utf-8")
session=loadSession()
try:
session.merge(proxy)
session.commit()
except MySQLdb.IntegrityError, e:
print e.message
else:
deleteProxy(item)
def execute(self, query, args=None):
try:
# args is None means no string interpolation
return self.cursor.execute(query, args)
except Database.OperationalError as e:
# Map some error codes to IntegrityError, since they seem to be
# misclassified and Django would prefer the more logical place.
if e.args[0] in self.codes_for_integrityerror:
six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
raise
def executemany(self, query, args):
try:
return self.cursor.executemany(query, args)
except Database.OperationalError as e:
# Map some error codes to IntegrityError, since they seem to be
# misclassified and Django would prefer the more logical place.
if e.args[0] in self.codes_for_integrityerror:
six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
raise
def check_constraints(self, table_names=None):
"""
Checks each table name in `table_names` for rows with invalid foreign
key references. This method is intended to be used in conjunction with
`disable_constraint_checking()` and `enable_constraint_checking()`, to
determine if rows with invalid references were entered while constraint
checks were off.
Raises an IntegrityError on the first invalid foreign key reference
encountered (if any) and provides detailed information about the
invalid reference in the error message.
Backends can override this method if they can more directly apply
constraint checking (e.g. via "SET CONSTRAINTS ALL IMMEDIATE")
"""
cursor = self.cursor()
if table_names is None:
table_names = self.introspection.table_names(cursor)
for table_name in table_names:
primary_key_column_name = self.introspection.get_primary_key_column(cursor, table_name)
if not primary_key_column_name:
continue
key_columns = self.introspection.get_key_columns(cursor, table_name)
for column_name, referenced_table_name, referenced_column_name in key_columns:
cursor.execute("""
SELECT REFERRING.`%s`, REFERRING.`%s` FROM `%s` as REFERRING
LEFT JOIN `%s` as REFERRED
ON (REFERRING.`%s` = REFERRED.`%s`)
WHERE REFERRING.`%s` IS NOT NULL AND REFERRED.`%s` IS NULL"""
% (primary_key_column_name, column_name, table_name, referenced_table_name,
column_name, referenced_column_name, column_name, referenced_column_name))
for bad_row in cursor.fetchall():
raise utils.IntegrityError("The row in table '%s' with primary key '%s' has an invalid "
"foreign key: %s.%s contains a value '%s' that does not have a corresponding value in %s.%s."
% (table_name, bad_row[0],
table_name, column_name, bad_row[1],
referenced_table_name, referenced_column_name))
def execute(self, query, args=None):
try:
# args is None means no string interpolation
return self.cursor.execute(query, args)
except Database.OperationalError as e:
# Map some error codes to IntegrityError, since they seem to be
# misclassified and Django would prefer the more logical place.
if e.args[0] in self.codes_for_integrityerror:
six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
raise
def executemany(self, query, args):
try:
return self.cursor.executemany(query, args)
except Database.OperationalError as e:
# Map some error codes to IntegrityError, since they seem to be
# misclassified and Django would prefer the more logical place.
if e.args[0] in self.codes_for_integrityerror:
six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
raise
def check_constraints(self, table_names=None):
"""
Checks each table name in `table_names` for rows with invalid foreign
key references. This method is intended to be used in conjunction with
`disable_constraint_checking()` and `enable_constraint_checking()`, to
determine if rows with invalid references were entered while constraint
checks were off.
Raises an IntegrityError on the first invalid foreign key reference
encountered (if any) and provides detailed information about the
invalid reference in the error message.
Backends can override this method if they can more directly apply
constraint checking (e.g. via "SET CONSTRAINTS ALL IMMEDIATE")
"""
cursor = self.cursor()
if table_names is None:
table_names = self.introspection.table_names(cursor)
for table_name in table_names:
primary_key_column_name = self.introspection.get_primary_key_column(cursor, table_name)
if not primary_key_column_name:
continue
key_columns = self.introspection.get_key_columns(cursor, table_name)
for column_name, referenced_table_name, referenced_column_name in key_columns:
cursor.execute("""
SELECT REFERRING.`%s`, REFERRING.`%s` FROM `%s` as REFERRING
LEFT JOIN `%s` as REFERRED
ON (REFERRING.`%s` = REFERRED.`%s`)
WHERE REFERRING.`%s` IS NOT NULL AND REFERRED.`%s` IS NULL"""
% (primary_key_column_name, column_name, table_name, referenced_table_name,
column_name, referenced_column_name, column_name, referenced_column_name))
for bad_row in cursor.fetchall():
raise utils.IntegrityError("The row in table '%s' with primary key '%s' has an invalid "
"foreign key: %s.%s contains a value '%s' that does not have a corresponding value in %s.%s."
% (table_name, bad_row[0],
table_name, column_name, bad_row[1],
referenced_table_name, referenced_column_name))
def get_buckets_acls(self, resource_name, timestamp):
"""Select the bucket acls from a bucket acls snapshot table.
Args:
resource_name (str): String of the resource name.
timestamp (str): String of timestamp, formatted as
YYYYMMDDTHHMMSSZ.
Returns:
list: List of bucket acls.
Raises:
MySQLError: An error with MySQL has occurred.
"""
bucket_acls = {}
cnt = 0
try:
bucket_acls_sql = select_data.BUCKET_ACLS.format(timestamp)
rows = self.execute_sql_with_fetch(resource_name,
bucket_acls_sql,
None)
for row in rows:
bucket_acl = bkt_acls.\
BucketAccessControls(bucket=row['bucket'],
entity=row['entity'],
email=row['email'],
domain=row['domain'],
role=row['role'],
project_number=row['project_number'])
bucket_acls[cnt] = bucket_acl
cnt += 1
except (DataError, IntegrityError, InternalError, NotSupportedError,
OperationalError, ProgrammingError) as e:
LOGGER.error(errors.MySQLError(resource_name, e))
return bucket_acls
def get_bigquery_acls(self, resource_name, timestamp):
"""Select the Big Query acls from a Big Query acls snapshot table.
Args:
resource_name (str): String of the resource name.
timestamp (str): String of timestamp, formatted as
YYYYMMDDTHHMMSSZ.
Returns:
dict: Dictionary keyed by the count of ACLs and then the ACLs.
Raises:
MySQLError: An error with MySQL has occurred.
"""
bigquery_acls = {}
cnt = 0
try:
bigquery_acls_sql = select_data.BIGQUERY_ACLS.format(timestamp)
rows = self.execute_sql_with_fetch(resource_name,
bigquery_acls_sql,
None)
except (DataError, IntegrityError, InternalError, NotSupportedError,
OperationalError, ProgrammingError) as e:
LOGGER.error(errors.MySQLError(resource_name, e))
for row in rows:
bigquery_acl = bq_acls.BigqueryAccessControls(
dataset_id=row['dataset_id'],
special_group=row['access_special_group'],
user_email=row['access_user_by_email'],
domain=row['access_domain'],
role=row['role'],
group_email=row['access_group_by_email'],
project_id=row['project_id'])
bigquery_acls[cnt] = bigquery_acl
cnt += 1
return bigquery_acls
def _get_cloudsql_instance_acl_map(self, resource_name, timestamp):
"""Create CloudSQL instance acl map.
Args:
resource_name (str): String of the resource name.
timestamp (str): String of timestamp, formatted as
YYYYMMDDTHHMMSSZ.
Returns:
dict: Map of instance acls.
Raises:
MySQLError: An error with MySQL has occurred.
"""
cloudsql_acls_map = {}
try:
cloudsql_acls_sql = select_data.CLOUDSQL_ACLS.format(timestamp)
rows = self.execute_sql_with_fetch(resource_name,
cloudsql_acls_sql,
None)
for row in rows:
acl_list = []
project_number = row['project_number']
instance_name = row['instance_name']
network = row['value']
hash_key = hash(str(project_number) + ',' + instance_name)
if hash_key in cloudsql_acls_map:
if network not in cloudsql_acls_map[hash_key]:
cloudsql_acls_map[hash_key].append(network)
else:
acl_list.append(network)
cloudsql_acls_map[hash_key] = acl_list
except (DataError, IntegrityError, InternalError, NotSupportedError,
OperationalError, ProgrammingError) as e:
LOGGER.error(errors.MySQLError(resource_name, e))
return cloudsql_acls_map
def load_data(self, resource_name, timestamp, data):
"""Load data into a snapshot table.
Args:
resource_name (str): String of the resource name.
timestamp (str): String of timestamp, formatted as
YYYYMMDDTHHMMSSZ.
data (iterable): An iterable or a list of data to be uploaded.
Raises:
MySQLError: When an error has occured while executing the query.
"""
with csv_writer.write_csv(resource_name, data) as csv_file:
try:
snapshot_table_name = self._create_snapshot_table_name(
resource_name, timestamp)
load_data_sql = load_data_sql_provider.provide_load_data_sql(
resource_name, csv_file.name, snapshot_table_name)
LOGGER.debug('SQL: %s', load_data_sql)
cursor = self.conn.cursor()
cursor.execute(load_data_sql)
self.conn.commit()
# TODO: Return the snapshot table name so that it can be tracked
# in the main snapshot table.
except (DataError, IntegrityError, InternalError,
NotSupportedError, OperationalError,
ProgrammingError) as e:
raise MySQLError(resource_name, e)
