def create_profile_hits(cur, feature, gene_id):
'''Create profile hit entries for a feature'''
detected_domains = parse_domains_detected(feature)
for domain in detected_domains:
domain['gene_id'] = gene_id
cur.execute("""
SELECT gene_id FROM antismash.profile_hits WHERE
gene_id = %(gene_id)s AND
name = %(name)s AND
evalue = %(evalue)s AND
bitscore = %(bitscore)s""", domain)
ret = cur.fetchone()
if ret is None:
try:
cur.execute("""
INSERT INTO antismash.profile_hits (gene_id, name, evalue, bitscore, seeds)
VALUES (%(gene_id)s, %(name)s, %(evalue)s, %(bitscore)s, %(seeds)s)""", domain)
except psycopg2.IntegrityError:
print(feature)
print(domain)
raise
python类IntegrityError()的实例源码
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEqual(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEqual(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def upsert_edit_project(self,filepath,filename,uuid,version,desc=None,opens_with=None):
cursor = self.conn.cursor()
matches=re.search(u'(\.[^\.]+)$',filename)
file_xtn=""
if matches is not None:
file_xtn=str(matches.group(1))
else:
raise ArgumentError("Filename %s does not appear to have a file extension" % filename)
typenum=self.project_type_for_extension(file_xtn,desc=desc,opens_with=opens_with)
try:
cursor.execute("insert into edit_projects (filename,filepath,type,lastseen,valid) values (%s,%s,%s,now(),true) returning id", (filename,filepath,typenum))
except psycopg2.IntegrityError as e:
self.conn.rollback()
cursor.execute("update edit_projects set lastseen=now(), valid=true where filename=%s and filepath=%s returning id", (filename,filepath))
result = cursor.fetchone()
id = result[0]
sqlcmd="update edit_projects set uuid=%s, version=%s where id=%s"
cursor.execute(sqlcmd, (uuid,version,id))
self.conn.commit()
return id
def log_project_issue(self,filepath,filename,problem="",detail="",desc=None,opens_with=None):
cursor = self.conn.cursor()
matches=re.search(u'(\.[^\.]+)$',filename)
file_xtn = ""
if matches is not None:
file_xtn=str(matches.group(1))
else:
raise ArgumentError("Filename %s does not appear to have a file extension" % filename)
typenum=self.project_type_for_extension(file_xtn,desc=desc,opens_with=opens_with)
try:
cursor.execute("""insert into edit_projects (filename,filepath,type,problem,problem_detail,lastseen,valid)
values (%s,%s,%s,%s,%s,now(),false) returning id""", (filename,filepath,typenum,problem,detail))
except psycopg2.IntegrityError as e:
print str(e)
print traceback.format_exc()
self.conn.rollback()
cursor.execute("""update edit_projects set lastseen=now(), valid=false, problem=%s, problem_detail=%s where filename=%s and filepath=%s returning id""", (problem,detail,filename,filepath))
#print cursor.mogrify("""update edit_projects set lastseen=now(), valid=false, problem=%s, problem_detail=%s where filename=%s and filepath=%s returning id""", (problem,detail,filename,filepath))
result=cursor.fetchone()
id = result[0]
self.conn.commit()
return id
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def __exit__(self, ty, val, tb):
end = time.time()
# Ignore the problem if we just added a duplicate
if ty is None:
log('SQL execution in [{}] completed without error. Duration: [{:.3f}]'.format(
self.proc, end - self.start), proc=self.proc, v=logging.D)
# Handle duplicate entry violations
elif (ty is psycopg2.IntegrityError) and self.ignore_duplicates:
if (val.pgcode in (errorcodes.UNIQUE_VIOLATION,
errorcodes.NOT_NULL_VIOLATION,
)):
log('SQL execution in [{}] completed. Null or Unique constraint hit [{}]. Duration: [{:.3f}]'.format(
self.proc, val.pgerror, end - self.start), proc=self.proc, v=logging.I)
return True
else:
log('Finished SQL execution in [{}] after [{:.3f}] seconds with [{}] error [{}]. Traceback: [{}]'.format(
self.proc, end - self.start, ty.__name__, str(val), traceback.format_tb(tb)),
proc=self.proc, v=logging.I)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEqual(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def test_error(self):
cur = self.conn.cursor()
cur.execute("insert into table1 values (%s)", (1, ))
self.wait(cur)
cur.execute("insert into table1 values (%s)", (1, ))
# this should fail
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
cur.execute("insert into table1 values (%s); "
"insert into table1 values (%s)", (2, 2))
# this should fail as well
self.assertRaises(psycopg2.IntegrityError, self.wait, cur)
# but this should work
cur.execute("insert into table1 values (%s)", (2, ))
self.wait(cur)
# and the cursor should be usable afterwards
cur.execute("insert into table1 values (%s)", (3, ))
self.wait(cur)
cur.execute("select * from table1 order by id")
self.wait(cur)
self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )])
cur.execute("delete from table1")
self.wait(cur)
def save_load(jid, load, minions=None):
'''
Save the load to the specified jid id
'''
with _get_serv(commit=True) as cur:
sql = '''INSERT INTO jids
(jid, load)
VALUES (%s, %s)'''
try:
cur.execute(sql, (jid, psycopg2.extras.Json(load)))
except psycopg2.IntegrityError:
# https://github.com/saltstack/salt/issues/22171
# Without this try:except: we get tons of duplicate entry errors
# which result in job returns not being stored properly
pass
def test_failed_commit(self):
# Test that we can recover from a failed commit.
# We use a deferred constraint to cause a failure on commit.
curs = self.conn.cursor()
curs.execute('SET CONSTRAINTS table2__table1_id__fk DEFERRED')
curs.execute('INSERT INTO table2 VALUES (2, 42)')
# The commit should fail, and move the cursor back to READY state
self.assertEqual(self.conn.status, STATUS_BEGIN)
self.assertRaises(psycopg2.IntegrityError, self.conn.commit)
self.assertEqual(self.conn.status, STATUS_READY)
# The connection should be ready to use for the next transaction:
curs.execute('SELECT 1')
self.assertEqual(curs.fetchone()[0], 1)
def test_failed_commit(self):
# Test that we can recover from a failed commit.
# We use a deferred constraint to cause a failure on commit.
curs = self.conn.cursor()
curs.execute('SET CONSTRAINTS table2__table1_id__fk DEFERRED')
curs.execute('INSERT INTO table2 VALUES (2, 42)')
# The commit should fail, and move the cursor back to READY state
self.assertEqual(self.conn.status, STATUS_BEGIN)
self.assertRaises(psycopg2.IntegrityError, self.conn.commit)
self.assertEqual(self.conn.status, STATUS_READY)
# The connection should be ready to use for the next transaction:
curs.execute('SELECT 1')
self.assertEqual(curs.fetchone()[0], 1)
def test_missing_column_required(self, db_session, missing_column_required):
with pytest.raises(psycopg2.IntegrityError):
dallinger.data.ingest_to_model(missing_column_required, dallinger.models.Participant)
def update_users(repository):
"""Update users of the integration in the database"""
if os.environ.get("OVER_HEROKU", False) is not False:
# Check if repository exists in database
query = r"INSERT INTO Users (repository, created_at) VALUES ('{}', now());" \
"".format(repository)
# cursor and conn are bultins, defined in app.py
try:
cursor.execute(query)
conn.commit()
except psycopg2.IntegrityError: # If already exists
conn.rollback()
def create_user(self, username: str, email: str, password: str):
"""
Create a new user. This method is called during the registration process.
Raises
------
LengthError
Raised when password length is less than 8 or greater than 72 characters.
Why 72? because bcrypt only works properly till 72.
"""
if len(password) < 8 or len(password) > 72:
raise LengthError("password", "Password length should be between 8 and 72 characters.")
if not self._is_valid_email(email):
raise ValidationError("Please enter a valid email-id.")
with self.conn:
with self.conn.cursor() as cur:
try:
cur.execute(
"""
INSERT INTO users (user_id, user_name, user_email, user_password_hash, user_timestamp,
user_avatar)
VALUES (%s, %s, %s, %s, %s, %s)
""",
(snowflake.generate(), username, email, self._hash_password(password), datetime.utcnow(),
self._hash_email(email))
)
return True
# For now, this happens when the unique email constraint
# is violated.
except psycopg2.IntegrityError:
print("This email_id already exists. Sorry bruh!")
return False
# TODO: verify_user is probably better off in `auth.py`
def perform_group_create(self, intent):
payload = '{"admins": [], "users": []}'
async with self.connection.acquire() as conn:
async with conn.cursor() as cur:
try:
await cur.execute("INSERT INTO groups VALUES (%s, %s);",
(intent.name, payload))
except IntegrityError:
raise GroupAlreadyExist('Group already exist.')
def perform_pubkey_add(self, intent):
async with self.connection.acquire() as conn:
async with conn.cursor() as cur:
try:
await cur.execute("INSERT INTO pubkeys VALUES (%s, %s);",
(intent.id, intent.key.decode()))
except IntegrityError:
raise PubKeyError('Identity `%s` already has a public key' % intent.id)
def perform_privkey_add(self, intent):
async with self.connection.acquire() as conn:
async with conn.cursor() as cur:
try:
await cur.execute("INSERT INTO privkeys VALUES (%s, %s);",
(intent.hash, intent.cipherkey))
except IntegrityError:
raise PrivKeyHashCollision('Hash collision, change your password and retry.')
def copy_table(conn_in, conn_out, table_name, constraint=None):
cursor_in = conn_in.cursor()
#Check that table exists
cursor_in.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name='%s';"
% table_name
)
if cursor_in.fetchone():
cursor_in.execute(
"SELECT * FROM \"%s\"%s;" % (table_name, "" if constraint is None
else " WHERE " + constraint)
)
with conn_out.cursor() as cursor_out:
for record in cursor_in:
values = record_to_string(record)
try:
cursor_out.execute(
"INSERT INTO \"%s\" VALUES (%s);" %
(table_name, values)
)
except psycopg2.IntegrityError as e:
conn_out.rollback()
if e.pgcode == '23505':
sys.stderr.write(
"ERROR: GeoPackage seems to be already imported. "
"Error message was: '%s'.\n" % e.message
)
sys.exit(1)
except Exception as e:
conn_out.rollback()
sys.stderr.write(
"ERROR: Input doesn't seem to be a valid GeoPackage. "
"Error message was: '%s'.\n" % e.message
)
sys.exit(1)
def test_failed_commit(self):
# Test that we can recover from a failed commit.
# We use a deferred constraint to cause a failure on commit.
curs = self.conn.cursor()
curs.execute('SET CONSTRAINTS table2__table1_id__fk DEFERRED')
curs.execute('INSERT INTO table2 VALUES (2, 42)')
# The commit should fail, and move the cursor back to READY state
self.assertEqual(self.conn.status, STATUS_BEGIN)
self.assertRaises(psycopg2.IntegrityError, self.conn.commit)
self.assertEqual(self.conn.status, STATUS_READY)
# The connection should be ready to use for the next transaction:
curs.execute('SELECT 1')
self.assertEqual(curs.fetchone()[0], 1)
def find_by_id(self, _id, attempt = 0):
with self.conn.cursor() as cur:
try:
cur.execute(
"""SELECT id FROM page WHERE url = '{}'""".format(_id)
)
return cur.fetchone()
except (psycopg2.IntegrityError, psycopg2.InternalError) as err:
if attempt < 5:
return self.find_by_id(_id, attempt + 1)
else:
raise err
