我们从Python开源项目中，提取了以下50个代码示例，用于说明如何使用psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT。
def handle(self, *args, **options): self.stdout.write(self.style.SUCCESS('Starting Schema creation..')) dbname = settings.DATABASES['default']['NAME'] user = settings.DATABASES['default']['USER'] password = settings.DATABASES['default']['PASSWORD'] host = settings.DATABASES['default']['HOST'] con = connect(dbname=dbname, user=user, host=host, password=password) self.stdout.write(self.style.SUCCESS('Adding schema {schema} to database {dbname}' .format(schema=settings.SCHEMA, dbname=dbname))) con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = con.cursor() cur.execute('CREATE SCHEMA {schema};'.format(schema=settings.SCHEMA)) cur.close() con.close() self.stdout.write(self.style.SUCCESS('All Done!'))
def handle(self, *args, **options): self.stdout.write(self.style.SUCCESS('Starting Schema deletion..')) dbname = settings.DATABASES['default']['NAME'] user = settings.DATABASES['default']['USER'] password = settings.DATABASES['default']['PASSWORD'] host = settings.DATABASES['default']['HOST'] con = connect(dbname=dbname, user=user, host=host, password=password) self.stdout.write(self.style.SUCCESS('Removing schema {schema} from database {dbname}' .format(schema=settings.SCHEMA, dbname=dbname))) con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = con.cursor() cur.execute('DROP SCHEMA {schema} CASCADE;'.format(schema=settings.SCHEMA)) cur.close() con.close() self.stdout.write(self.style.SUCCESS('All Done.'))
def handle(self, *args, **options): self.stdout.write(self.style.SUCCESS('Starting DB creation..')) dbname = settings.DATABASES['default']['NAME'] user = settings.DATABASES['default']['USER'] password = settings.DATABASES['default']['PASSWORD'] host = settings.DATABASES['default']['HOST'] self.stdout.write(self.style.SUCCESS('Connecting to host..')) con = connect(dbname='postgres', user=user, host=host, password=password) con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) self.stdout.write(self.style.SUCCESS('Creating database')) cur = con.cursor() cur.execute('CREATE DATABASE ' + dbname) cur.close() con.close() self.stdout.write(self.style.SUCCESS('All done!'))
def get_stats(): print "hit1" conn, cur = None, None stats = {} try: conn = psycopg2.connect(config['connection_string']) conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = conn.cursor() stats = _get_stats(cur) finally: if cur is not None: cur.close() if conn is not None: conn.close() return stats
def notify(self, name, sec=0, payload=None): """Send a notification to the database, eventually after some time.""" if payload is None: payload = '' else: payload = ", %r" % payload script = ("""\ import time time.sleep(%(sec)s) import %(module)s as psycopg2 import %(module)s.extensions as ext conn = psycopg2.connect(%(dsn)r) conn.set_isolation_level(ext.ISOLATION_LEVEL_AUTOCOMMIT) print conn.get_backend_pid() curs = conn.cursor() curs.execute("NOTIFY " %(name)r %(payload)r) curs.close() conn.close() """ % { 'module': psycopg2.__name__, 'dsn': dsn, 'sec': sec, 'name': name, 'payload': payload}) return Popen([sys.executable, '-c', script_to_py3(script)], stdout=PIPE)
def write_to_db(self, result, table, primary_key=None): if len(result) == 0: return result_string = ['(' + ','.join(['\'%s\'' % c for c in r]) + ')' for r in result] result_string = ',\n'.join(result_string) query = """ INSERT INTO %s VALUES %s """ % (table, result_string) if primary_key is not None: query += "\nON CONFLICT (%s) DO NOTHING\n" % primary_key with connect(self.connection_string) as con: con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) with con.cursor() as cur: cur.execute(query) cur.close()
def extract_distances(self): query = """ SELECT o.stationfrom, o.vehicle, o.date, COUNT(*) AS freq FROM occupancy o INNER JOIN connection c ON o.stationfrom = c.departurestop AND o.date = c.departuredate AND o.vehicle = c.route WHERE c.trip <> '' GROUP BY o.stationfrom, o.vehicle, o.date ORDER by freq """ with connect(self.connection_string) as con: con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) with con.cursor() as cur: cur.execute(query) for row in cur: stationfrom = row[0] vehicle = row[1] date = row[2] print row distances_to_station = self.get_distances_to_station(con, date, stationfrom, vehicle) yield distances_to_station cur.close()
def remove_duplicate_connections(self): query = """ DELETE FROM connection WHERE exists(SELECT 1 FROM connection t2 WHERE t2.arrivaltime = connection.arrivaltime AND t2.arrivalstop = connection.arrivalstop AND t2.departuretime = connection.departuretime AND t2.departurestop = connection.departurestop AND t2.ctid > connection.ctid); """ with connect(self.connection_string) as con: con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) with con.cursor() as cur: cur.execute(query) cur.close()
def tables_exist(self): query = """ SELECT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'connection' ); """ with connect(self.connection_string) as con: con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) with con.cursor() as cur: cur.execute(query) result = cur.fetchone() if result is not None: exists = result[0] return exists return False
def create_postgres_db(connection_dict, config): if check_db_or_user_exists(connection_dict["db_name"], connection_dict["db_username"], config): raise ValueError("db or user already exists") con = _create_pg_connection(config) con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = con.cursor() create_role = "CREATE USER {db_username} WITH PASSWORD '{db_pwd}';".format(**connection_dict) drop_role = "DROP ROLE {db_username};".format(**connection_dict) grant_role = 'GRANT {db_username} TO "{postgraas_user}";'.format( db_username=connection_dict['db_username'], postgraas_user=get_normalized_username(config['username']) ) create_database = "CREATE DATABASE {db_name} OWNER {db_username};".format(**connection_dict) try: cur.execute(create_role) cur.execute(grant_role) except psycopg2.ProgrammingError as e: raise ValueError(e.args[0]) # cleanup role in case database creation fails # saidly 'CREATE DATABASE' cannot run inside a transaction block try: cur.execute(create_database) except psycopg2.ProgrammingError as e: cur.execute(drop_role) raise ValueError(e.args[0])
def run_rules(job_id, schema_name): meta_conn = psycopg2.connect("dbname='validator' user='testUser' host='localhost' password='testPwd'") meta_conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) meta_c = meta_conn.cursor() meta_c.execute('UPDATE jobs SET status=\'starting_rules\' WHERE job_id=%d' % job_id) conn = psycopg2.connect("dbname='job_%d' user='testUser' host='localhost' password='testPwd'" % job_id) c = conn.cursor() reader = csv.reader(open('rules/%s.csv' % schema_name, 'rb'), quotechar='"', delimiter=',', quoting=csv.QUOTE_ALL, skipinitialspace=True) header = reader.next() for row in reader: sql = row[header.index('sql')] print "Running rule %s: %s" % (row[header.index('id')], sql) c.execute(sql) invalid_count = 0 for row in c.fetchall(): invalid_count += 1 print '==> Found %d invalid rows.' % invalid_count conn.close() meta_c.execute("UPDATE jobs SET status='finished_rules' WHERE job_id=%d" % job_id) meta_conn.close()
def delete_user(user_name: str): """Deletes a user's database, role and removes the entry from the user database. Returns the `http_port` variable to deny access to ports. Parameters ---------- user_name : str Name of user account """ with psycopg2.connect(**PSQL_CONN) as conn: conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cur = conn.cursor() cur.execute("""DELETE FROM {} WHERE user_name = '{}';""" .format(PSQL_TABLE, user_name)) cur.execute("""DROP DATABASE {};""".format(user_name)) cur.execute("""DROP ROLE {};""".format(user_name)) conn.close() click.secho('SUCCESS: User {} was removed from database.' .format(user_name), fg='green')
def _isolation_lookup(self): extensions = self._psycopg2_extensions() return { 'AUTOCOMMIT': extensions.ISOLATION_LEVEL_AUTOCOMMIT, 'READ COMMITTED': extensions.ISOLATION_LEVEL_READ_COMMITTED, 'READ UNCOMMITTED': extensions.ISOLATION_LEVEL_READ_UNCOMMITTED, 'REPEATABLE READ': extensions.ISOLATION_LEVEL_REPEATABLE_READ, 'SERIALIZABLE': extensions.ISOLATION_LEVEL_SERIALIZABLE }
def cursor(self, autocommit=False, readonly=False): if self._connpool is None: self.connect() conn = self._connpool.getconn() if autocommit: conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) else: conn.set_isolation_level(ISOLATION_LEVEL_REPEATABLE_READ) cursor = Cursor(self._connpool, conn, self) if readonly: cursor.execute('SET TRANSACTION READ ONLY') return cursor
def autocommit(self, on_off=1): """autocommit(on_off=1) -> switch autocommit on (1) or off (0)""" if on_off > 0: self.set_isolation_level(_ext.ISOLATION_LEVEL_AUTOCOMMIT) else: self.set_isolation_level(_ext.ISOLATION_LEVEL_READ_COMMITTED)
def test_set_isolation_level_autocommit(self): conn = self.connect() curs = conn.cursor() conn.set_isolation_level(ext.ISOLATION_LEVEL_AUTOCOMMIT) self.assertEqual(conn.isolation_level, ext.ISOLATION_LEVEL_DEFAULT) self.assertTrue(conn.autocommit) conn.isolation_level = 'serializable' self.assertEqual(conn.isolation_level, ext.ISOLATION_LEVEL_SERIALIZABLE) self.assertTrue(conn.autocommit) curs.execute('show transaction_isolation;') self.assertEqual(curs.fetchone()[0], 'serializable')
def test_set_isolation_level_abort(self): conn = self.connect() cur = conn.cursor() self.assertEqual(ext.TRANSACTION_STATUS_IDLE, conn.get_transaction_status()) cur.execute("insert into isolevel values (10);") self.assertEqual(ext.TRANSACTION_STATUS_INTRANS, conn.get_transaction_status()) conn.set_isolation_level( psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE) self.assertEqual(psycopg2.extensions.TRANSACTION_STATUS_IDLE, conn.get_transaction_status()) cur.execute("select count(*) from isolevel;") self.assertEqual(0, cur.fetchone()[0]) cur.execute("insert into isolevel values (10);") self.assertEqual(psycopg2.extensions.TRANSACTION_STATUS_INTRANS, conn.get_transaction_status()) conn.set_isolation_level( psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) self.assertEqual(psycopg2.extensions.TRANSACTION_STATUS_IDLE, conn.get_transaction_status()) cur.execute("select count(*) from isolevel;") self.assertEqual(0, cur.fetchone()[0]) cur.execute("insert into isolevel values (10);") self.assertEqual(psycopg2.extensions.TRANSACTION_STATUS_IDLE, conn.get_transaction_status()) conn.set_isolation_level( psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED) self.assertEqual(psycopg2.extensions.TRANSACTION_STATUS_IDLE, conn.get_transaction_status()) cur.execute("select count(*) from isolevel;") self.assertEqual(1, cur.fetchone()[0]) self.assertEqual(conn.isolation_level, psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)
def test_isolation_level_autocommit(self): cnn1 = self.connect() cnn2 = self.connect() cnn2.set_isolation_level(ext.ISOLATION_LEVEL_AUTOCOMMIT) cur1 = cnn1.cursor() cur1.execute("select count(*) from isolevel;") self.assertEqual(0, cur1.fetchone()[0]) cnn1.commit() cur2 = cnn2.cursor() cur2.execute("insert into isolevel values (10);") cur1.execute("select count(*) from isolevel;") self.assertEqual(1, cur1.fetchone()[0])
def autocommit(self, conn): """Set a connection in autocommit mode.""" conn.set_isolation_level(extensions.ISOLATION_LEVEL_AUTOCOMMIT)
def test_set_isolation_level_autocommit(self): conn = self.connect() curs = conn.cursor() conn.set_isolation_level(ext.ISOLATION_LEVEL_AUTOCOMMIT) self.assertEqual(conn.isolation_level, ext.ISOLATION_LEVEL_DEFAULT) self.assert_(conn.autocommit) conn.isolation_level = 'serializable' self.assertEqual(conn.isolation_level, ext.ISOLATION_LEVEL_SERIALIZABLE) self.assert_(conn.autocommit) curs.execute('show transaction_isolation;') self.assertEqual(curs.fetchone()[0], 'serializable')
def _isolation_lookup(self): from psycopg2 import extensions return { 'AUTOCOMMIT': extensions.ISOLATION_LEVEL_AUTOCOMMIT, 'READ COMMITTED': extensions.ISOLATION_LEVEL_READ_COMMITTED, 'READ UNCOMMITTED': extensions.ISOLATION_LEVEL_READ_UNCOMMITTED, 'REPEATABLE READ': extensions.ISOLATION_LEVEL_REPEATABLE_READ, 'SERIALIZABLE': extensions.ISOLATION_LEVEL_SERIALIZABLE }
def connect_and_execute(query, database='postgres'): """Connect to database, execute given query and return fetched results""" conn = connect(database=database) try: conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) cursor = conn.cursor(cursor_factory=RealDictCursor) cursor.execute(query) return cursor.fetchall() finally: conn.close()
def get_last_departure_time(self): query = """ SELECT MAX(departuretime) FROM connection """ with connect(self.connection_string) as con: con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) with con.cursor() as cur: cur.execute(query) result = cur.fetchone() if result is not None: last_departure_time = result[0] return last_departure_time return None
def _isolation_lookup(self): extensions = __import__('psycopg2.extensions').extensions return { 'AUTOCOMMIT': extensions.ISOLATION_LEVEL_AUTOCOMMIT, 'READ COMMITTED': extensions.ISOLATION_LEVEL_READ_COMMITTED, 'READ UNCOMMITTED': extensions.ISOLATION_LEVEL_READ_UNCOMMITTED, 'REPEATABLE READ': extensions.ISOLATION_LEVEL_REPEATABLE_READ, 'SERIALIZABLE': extensions.ISOLATION_LEVEL_SERIALIZABLE }
