def main(reactor): connections = [] for x in range(25): conn = txpostgres.Connection() db = yield conn.connect('dbname=postgres') connections.append(db) yield connections[0].runOperation(_create_table) # a 'real' generator, round-robin all connections def connection_generator(): while True: for c in connections: yield c connect = connection_generator() inserts = [] for item in range(1000): db = next(connect) d = db.runOperation( 'INSERT INTO todo (todo, created_at) ' 'VALUES (%s, NOW());', [item], ) dl.append(d) start = reactor.seconds() yield defer.DeferredList(dl) diff = reactor.seconds() - start print("Took {}s".format(diff))
def main(reactor): conn = txpostgres.Connection() db = yield conn.connect('dbname=postgres') print("Databse connected {}".format(db)) yield db.runOperation(_create_table) dl = [] # for item in ['teach PyYYC', 'find beers', '???', 'profit']: for item in range(1000): d = db.runOperation( 'INSERT INTO todo (todo, created_at) VALUES (%s, NOW());', [item], ) dl.append(d) print("All inserts queued") s = reactor.seconds() yield defer.DeferredList(dl) diff = reactor.seconds() - s print("done creating database") print("Querying all TODOs:") res = yield db.runQuery('SELECT * from todo;') expected = 1 last = None for _id, todo, created_at in res: print(" {}: '{}' at '{}'".format(_id, todo, created_at)) if last and created_at < last: print("out of order") sys.exit(3) last = created_at if _id != expected: print("DING") sys.exit(4) expected += 1 print("done {}".format(diff))
def test_connectionRemovingReader(self): """ The connection is not reading from the socket while a cursor is running. """ class ExclusiveCursor(txpostgres.Cursor): testcase = self def doRead(self): for reader in self.reactor.getReaders(): if isinstance(reader, txpostgres.Connection): self.testcase.fail( "doRead called on Cursor " "while a Connection is among readers: %r" % self.reactor.getReaders()) return txpostgres.Cursor.doRead(self) conn = txpostgres.Connection() conn.cursorFactory = ExclusiveCursor d = conn.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) # use something more complex than select 1 or otherwise the query might # complete in a single reactor cycle d.addCallback(lambda _: conn.cursor().execute("select pg_sleep(0.1)")) return d.addCallback(lambda _: conn.close())
def _connect(self, **kwargs): from txpostgres import txpostgres, reconnection from txpostgres.reconnection import DeadConnectionDetector class LoggingDetector(DeadConnectionDetector): def startReconnecting(self, f): print("ERROR: database connection is down (error: {0})" .format(f.value)) return DeadConnectionDetector.startReconnecting(self, f) def reconnect(self): print("INFO: Reconnecting...") return DeadConnectionDetector.reconnect(self) def connectionRecovered(self): print("INFO: connection recovered") return DeadConnectionDetector.connectionRecovered(self) self.connection = txpostgres.Connection(detector=LoggingDetector()) d = self.connection.connect(host=kwargs['host'], database=self.name, user=kwargs['user'], password=kwargs['password']) d.addErrback(self.connection.detector.checkForDeadConnection) d.addErrback(self.connectionError) yield d print("INFO: Database connected -- %s" %self.name)
def test_errors(self): """ Errors from psycopg2's poll() make connect() return failures. Errors on creating the psycopg2 connection too. Unexpected results from poll() also make connect() return a failure. """ conn = txpostgres.Connection() def setFactory(conn, factory): conn.connectionFactory = factory class BadPollable(object): closed = 1 def __init__(*args, **kwars): pass def poll(self): raise RuntimeError("booga") def close(self): pass setFactory(conn, BadPollable) d = conn.connect() d = self.assertFailure(d, RuntimeError) d.addCallback(lambda _: conn.close()) class BadThing(object): closed = 1 def __init__(*args, **kwargs): raise RuntimeError("wooga") def close(self): pass d.addCallback(lambda _: setFactory(conn, BadThing)) d.addCallback(lambda _: conn.connect()) d = self.assertFailure(d, RuntimeError) class BrokenPollable(object): closed = 1 def __init__(*args, **kwars): pass def poll(self): return "tee hee hee" def close(self): pass d.addCallback(lambda _: setFactory(conn, BrokenPollable)) d.addCallback(lambda _: conn.connect()) return self.assertFailure(d, txpostgres.UnexpectedPollResult)
def onJoin(self, details): print( "PostgreSQL database adapter [publisher role] connected to router") dbconfig = self.config.extra['database'] ## check if the config contains environment variables instead of ## straight strings (e.g. $DBNAME), and if so, try to fill in the actual ## value from environment ## pat = re.compile("^\$([A-Z0-9_]+)$") for k in ['host', 'port', 'database', 'user', 'password']: if k in dbconfig: if type(dbconfig[k]) in (str, unicode): match = pat.match(dbconfig[k]) if match and match.groups(): envvar = match.groups()[0] if envvar in os.environ: dbconfig[k] = os.environ[envvar] if k != 'password': val = dbconfig[k] else: val = len(dbconfig[k]) * '*' print( "database configuration parameter '{}' set to '{}' from environment variable {}" .format(k, val, envvar)) else: print( "warning: database configuration parameter '{}' should have been read from enviroment variable {}, but the latter is not set" .format(k, envvar)) conn = txpostgres.Connection() try: yield conn.connect(**dbconfig) except Exception as e: print("could not connect to database: {0}".format(e)) self.leave() return else: print( "PostgreSQL database adapter [publisher role] connected to database" ) conn.addNotifyObserver(self._on_notify) try: yield conn.runOperation("LISTEN {0}".format( self.CHANNEL_PUBSUB_EVENT)) except Exception as e: print("failed to listen on channel '{0}': {1}".format( self.CHANNEL_PUBSUB_EVENT, e)) self.leave() else: print( "ok, pusher is listening on PostgreSQL NOTIFY channel '{0}'' ..." .format(self.CHANNEL_PUBSUB_EVENT))
def setUp(self): self.notifyconn = txpostgres.Connection() self.notifies = [] d = self.notifyconn.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) return d.addCallback(lambda _: _SimpleDBSetupMixin.setUp(self))
def restoreConnection(self, res): """ Restore the connection to the database and return whatever argument has been passed through. Useful as an addBoth handler for tests that disconnect from the database. """ self.conn = txpostgres.Connection() d = self.conn.connect(database='webber') d.addErrback(log.err) return d.addCallback(lambda _: res)
def connect_and_observe(self, db_config, channel, fun): # connect to database # conn = txpostgres.Connection() db_conn_params = { u'user': db_config['user'], u'password': db_config['password'], u'host': db_config['host'], u'port': db_config['port'], u'database': db_config['database'], } try: yield conn.connect(**db_conn_params) except Exception as e: raise Exception("database connection failed: {}".format(e)) else: self.log.debug("Connected to database") # acquire exclusive run lock # res = yield conn.runQuery("SELECT pg_try_advisory_lock(%s, %s)", (self.PG_LOCK_GROUP, db_config['adapter_xlock'])) if not res[0][0]: locker_pid, locker_user_id, locker_user_name, locker_app_name = None, None, None, None res = yield conn.runQuery("SELECT pid FROM pg_locks WHERE locktype = 'advisory' AND classid = %s AND objid = %s", (self.PG_LOCK_GROUP, db_config['adapter_xlock'])) if res: locker_pid = res[0][0] if locker_pid: res = yield conn.runQuery("SELECT usesysid, usename, application_name FROM pg_stat_activity WHERE pid = %s", (locker_pid,)) if res: locker_user_id, locker_user_name, locker_app_name = res[0] self.log.error('A database session already holds the run lock for us (pid={pid}, userid={userid}, username={username}, appname="{appname}")', pid=locker_pid, userid=locker_user_id, username=locker_user_name, appname=locker_app_name) raise Exception("Only one instance of this adapter can be connected to a given database") else: self.log.debug("Obtained exclusive run lock on ({key1}, {key2})", key1=self.PG_LOCK_GROUP, key2=db_config['adapter_xlock']) # upgrade database schema if needed # schema_version = yield self._check_and_upgrade_schema(db_config, conn) self.log.info("Running on schema version {schema_version}", schema_version=schema_version) # add channel listener # conn.addNotifyObserver(fun) try: yield conn.runOperation("LISTEN {0}".format(self._db_config['adapter_channel'])) except Exception as e: self.log.error("Failed to listen on channel '{0}': {1}".format(self._db_config['adapter_channel'], e)) self.leave() else: self.log.debug("Listening on PostgreSQL NOTIFY channel '{0}'' ...".format(self._db_config['adapter_channel']))
def __init__(self): self.pw_check = PasswordHash().check_password self._forums_conn = txpostgres.Connection() self._forums_conn.connect(host=settings.phpbb_host, database=settings.phpbb_db, user=settings.phpbb_user, password=settings.phpbb_pass) self._lobby_conn = txpostgres.Connection() self._lobby_conn.connect(host=settings.lobby_host, database=settings.lobby_db, user=settings.lobby_user, password=settings.lobby_pass) # Start the cleanup interval. self._cleanup_call = LoopingCall(self._cleanup) self._cleanup_call.start(settings.cleanup_interval, now=False) # and run it once in 2 seconds. reactor.callLater(2, self._cleanup)
def test_simpleConnection(self): """ Just connecting and disconnecting works. """ conn = txpostgres.Connection() d = conn.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) d.addCallback(lambda c: c.close()) return d
def restoreConnection(self, res): """ Restore the connection to the database and return whatever argument has been passed through. Useful as an addBoth handler for tests that disconnect from the database. """ self.conn = txpostgres.Connection() d = self.conn.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) return d.addCallback(lambda _: res)
def test_openRunCloseOpen(self): conn = txpostgres.Connection() connargs = dict(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) d = conn.connect(**connargs) d.addCallback(lambda _: conn.runQuery("select 1")) # make sure the txpostgres.Cursor created by runQuery got closed, # otherwise it will still be polled and will result in an error d.addCallback(lambda _: conn.close()) d.addCallback(lambda _: conn.connect(**connargs)) return d.addCallback(lambda _: conn.close())
def main(reactor): conn = txpostgres.Connection() db = yield conn.connect('dbname=postgres') yield db.runOperation(_create_table) #! to_insert = ['teach PyYYC', 'find beers', '???', 'profit'] #! for item in to_insert: yield db.runOperation( 'INSERT INTO todo (todo, created_at) ' 'VALUES (%s, NOW());', [item], )
def main(reactor, server='tcp:8000'): pool = ConnectionPool(dbname='asdf') conn = txpostgres.Connection() yield gatherResults([ pool.start(), conn.connect(dbname='asdf', cursor_factory=NamedTupleCursor) ]) res = yield makeEntryPoint(pool, conn) site = Site(res) ep = serverFromString(reactor, server) ep.listen(site) yield Deferred() # wait forever
def connect_raw(db_name, db_user, db_pass): """ Connect to the database bypassing the connection pool (e.g., for adding a notify observer). """ try: conn_str = ("dbname='{0}' user='******' password='******' host='{3}' port='{4}'".format(db_name or POSTGRES_DB, db_user, db_pass, HOST, PORT)) conn = txpostgres.Connection() connection = conn.connect(conn_str) return connection except Exception as e: logging.error("DB: Error connecting to {0} as {1} ({2})".format(db_name, db_user, e)) d = Deferred() failure = Failure(e) d.errback(failure) return d
def test_sameObserverAddedTwice(self): """ Adding the same observer twice results in just one registration. """ c = txpostgres.Connection() def observer(notify): pass self.assertEquals(len(c.getNotifyObservers()), 0) c.addNotifyObserver(observer) c.addNotifyObserver(observer) self.assertEquals(len(c.getNotifyObservers()), 1)
def _newConnection(self, conn_str): """ Makes a new connection to the DB and then puts it in the 'free' pool of this conn_str. """ logging.debug("IndxConnectionPool _newConnection") # lock with the semaphore before calling this return_d = Deferred() def close_old_cb(failure): failure.trap(psycopg2.OperationalError, Exception) # couldn't connect, so close an old connection first logging.error( "IndxConnectionPool error close_old_cb: {0} - state of conns is: {1}" .format(failure.value, self.connections)) logging.error("IndxConnectionPool connections: {0}".format( "\n".join( map(lambda name: self.connections[name].__str__(), self.connections)))) def closed_cb(empty): # closed, so try connecting again self._newConnection(conn_str).addCallbacks( return_d.callback, return_d.errback) closed_d = self._closeOldConnection() closed_d.addCallbacks(closed_cb, return_d.errback) try: # try to connect def connected_cb(connection): logging.debug( "IndxConnectionPool _newConnection connected_cb, connection: {0}" .format(connection)) self.connections[conn_str].getFree().append(connection) return_d.callback(connection) conn = txpostgres.Connection() connection_d = conn.connect(conn_str) connection_d.addCallbacks(connected_cb, close_old_cb) except Exception as e: # close an old connection first logging.debug( "IndxConnectionPool Exception, going to call close_old_cb: ({0})" .format(e)) close_old_cb(Failure(e)) return return_d
def test_closeTwice(self): """ Calling close() on the connection twice does not result in an error. """ conn = txpostgres.Connection() d = conn.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) def closeTwice(_): conn.close() conn.close() d.addCallback(closeTwice) return d.addCallback(lambda _: self.assertTrue(conn.closed))
def main(reactor): conn = txpostgres.Connection() db = yield conn.connect('dbname=postgres') yield db.runOperation(_create_table) to_insert = ['teach PyYYC', 'find beers', '???', 'profit'] for item in to_insert: yield db.runOperation( 'INSERT INTO todo (todo, created_at) ' 'VALUES (%s, NOW());', [item], ) #! answer = yield db.runQuery('SELECT * from todo') print("TODOs:") for id_, text, created in answer: print(" {}: '{}' at '{}'".format(id_, text, created))
def checkErrorAndHotswap(f): f.trap(txpostgres.RollbackFailed) e = f.value self.assertIdentical(e.connection.cursorFactory, NotRollingBackCursor) errors = self.flushLoggedErrors() self.assertEquals(len(errors), 1) self.assertEquals(errors[0].value.args[0], "boom") pool.remove(e.connection) e.connection.close() c = txpostgres.Connection() self.assertNotIdentical(c.cursorFactory, NotRollingBackCursor) d = c.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) return d.addCallback(lambda c: pool.add(c))
def test_multipleConnections(self): """ Trying to connect twice raises an exception, but after closing you can connect again. """ conn = txpostgres.Connection() d = conn.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) d.addCallback(lambda c: conn.connect( user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME)) d = self.failUnlessFailure(d, txpostgres.AlreadyConnected) d.addCallback(lambda _: conn.close()) d.addCallback(lambda _: conn.connect( user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME)) return d.addCallback(lambda c: c.close())
def test_connectionSetup(self): """ The created connection should be asynchronous and in autocommit mode and the C{Deferred} returned from connect() should fire with the connection itself. """ conn = txpostgres.Connection() d = conn.connect(user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME) def doChecks(c): self.assertIdentical(c, conn) self.assertTrue(c. async) self.assertEquals(c.isolation_level, 0) return c d.addCallback(doChecks) return d.addCallback(lambda c: c.close())
def onJoin(self, details): print("session joined") config = self.config.extra conn = txpostgres.Connection() try: yield conn.connect(**config['database']) except Exception as e: print("could not connect to database: {0}".format(e)) self.leave() return else: print("connected to database") conn.addNotifyObserver(self._on_notify) try: yield conn.runOperation("LISTEN {0}".format(self.CHANNEL_PUBSUB_EVENT)) except Exception as e: print("failed to listen on channel '{0}': {1}".format(self.CHANNEL_PUBSUB_EVENT, e)) self.leave() else: print("ok, pusher is listening on PostgreSQL NOTIFY channel '{0}'' ...".format(self.CHANNEL_PUBSUB_EVENT))
def test_removeNonexistentObserver(self): """ Removing an observer twice is valid and results in the observer being removed. Removing one that does not exist at all is valid as well. """ c = txpostgres.Connection() def observer1(notify): pass def observer2(notify): pass c.addNotifyObserver(observer1) c.addNotifyObserver(observer2) self.assertEquals(len(c.getNotifyObservers()), 2) c.removeNotifyObserver(observer1) c.removeNotifyObserver(observer1) c.removeNotifyObserver(lambda _: _) self.assertEquals(len(c.getNotifyObservers()), 1) self.assertIn(observer2, c.getNotifyObservers())
def main(reactor): conn = txpostgres.Connection() db = yield conn.connect('dbname=postgres')
def get_db_connection(): conn = txpostgres.Connection() d = conn.connect('host=localhost port=5432 user=postgres dbname=jsonstore') return conn, d
from txpostgres import txpostgres from twisted.internet import reactor from twisted.python import log, util # connect to the database conn = txpostgres.Connection() d = conn.connect('dbname=postgres') # run the query and print the result d.addCallback(lambda _: conn.runQuery('select tablename from pg_tables')) d.addCallback(lambda result: util.println('All tables:', result)) # close the connection, log any errors and stop the reactor d.addCallback(lambda _: conn.close()) d.addErrback(log.err) d.addBoth(lambda _: reactor.stop()) # start the reactor to kick off connection estabilishing reactor.run()
def error(f): print '-> query failed with %r' % f.value def connectionError(f): print '-> connecting failed with %r' % f.value def runLoopingQuery(conn): d = conn.runQuery('select 1') d.addCallbacks(result, error) def connected(_, conn): print '-> connected, running a query periodically' lc = task.LoopingCall(runLoopingQuery, conn) return lc.start(2) # connect to the database using reconnection conn = txpostgres.Connection(detector=LoggingDetector()) d = conn.connect('dbname=postgres') # if the connection failed, log the error and start reconnecting d.addErrback(conn.detector.checkForDeadConnection) d.addErrback(connectionError) d.addCallback(connected, conn) # process events until killed reactor.run()