How to use pg8000 - 10 common examples

''' dbapi2 tests '''

print 'psycopg2'
conn = psycopg2.connect("dbname=test user=postgres host=127.0.0.1")
cur = conn.cursor()
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE, cur)
cur.execute("CREATE TEMPORARY TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (100, "abc'def"))
cur.execute("SELECT * FROM test;")
cur.fetchone()
conn.commit()
cur.close()
conn.close()
print 'pg8000'
conn = pg8000.DBAPI.connect(host="localhost", user="test", password="test")
cursor = conn.cursor()
cursor.execute("CREATE TEMPORARY TABLE book (id SERIAL, title TEXT)")
cursor.execute(
     "INSERT INTO book (title) VALUES (%s), (%s) RETURNING id, title",
     ("Ender's Game", "Speaker for the Dead"))
for row in cursor:
    id, title = row
conn.commit()
cursor.execute("SELECT now()")
cursor.fetchone()
cursor.execute("SELECT now() - %s", (datetime.date(1980, 4, 27),))
cursor.fetchone()
pg8000.DBAPI.paramstyle = "numeric"
cursor.execute("SELECT array_prepend(:1, :2)", (500, [1, 2, 3, 4],))
cursor.fetchone()
cursor.close()

def testPyformat(self):
        orig_paramstyle = pg8000.paramstyle
        try:
            pg8000.paramstyle = "pyformat"
            c1 = self.db.cursor()
            c1.execute(
                "SELECT f1, f2, f3 FROM t1 WHERE f1 > %(f1)s", {"f1": 3})
            while 1:
                row = c1.fetchone()
                if row is None:
                    break
                f1, f2, f3 = row
            self.db.commit()
        finally:
            pg8000.paramstyle = orig_paramstyle
            c1.close()

def testFormat(self):
        orig_paramstyle = pg8000.paramstyle
        try:
            pg8000.paramstyle = "format"
            c1 = self.db.cursor()
            c1.execute("SELECT f1, f2, f3 FROM t1 WHERE f1 > %s", (3,))
            while 1:
                row = c1.fetchone()
                if row is None:
                    break
                f1, f2, f3 = row
            self.db.commit()
        finally:
            pg8000.paramstyle = orig_paramstyle
            c1.close()

def testBrokenPipe(self):
        db1 = pg8000.connect(**db_connect)
        db2 = pg8000.connect(**db_connect)

        try:
            cur1 = db1.cursor()
            cur2 = db2.cursor()

            cur1.execute("select pg_backend_pid()")
            pid1 = cur1.fetchone()[0]

            cur2.execute("select pg_terminate_backend(%s)", (pid1,))
            try:
                cur1.execute("select 1")
            except Exception as e:
                self.assertTrue(isinstance(e, (socket.error, struct.error)))

            cur2.close()

def poststart(self):
        with closing(pg8000.connect(**self.dsn(database='postgres'))) as conn:
            conn.autocommit = True
            with closing(conn.cursor()) as cursor:
                cursor.execute("SELECT COUNT(*) FROM pg_database WHERE datname='test'")
                if cursor.fetchone()[0] <= 0:
                    cursor.execute('CREATE DATABASE test')

def setUp(self):
        self.db = pg8000.connect(**db_connect)

        # Neither Windows nor Jython 2.5.3 have a time.tzset() so skip
        if hasattr(time, 'tzset'):
            os.environ['TZ'] = "UTC"
            time.tzset()
            self.HAS_TZSET = True
        else:
            self.HAS_TZSET = False

        try:
            c = self.db.cursor()
            try:
                c = self.db.cursor()
                c.execute("DROP TABLE t1")
            except pg8000.DatabaseError:
                e = exc_info()[1]

self.assertEqual('127.0.0.1', params['host'])
            self.assertEqual(pgsql.settings['port'], params['port'])
            self.assertEqual('postgres', params['user'])

            # connect to postgresql (w/ psycopg2)
            conn = psycopg2.connect(**pgsql.dsn())
            self.assertIsNotNone(conn)
            self.assertRegexpMatches(pgsql.read_bootlog(), 'is ready to accept connections')
            conn.close()

            # connect to postgresql (w/ sqlalchemy)
            engine = sqlalchemy.create_engine(pgsql.url())
            self.assertIsNotNone(engine)

            # connect to postgresql (w/ pg8000)
            conn = pg8000.connect(**pgsql.dsn())
            self.assertIsNotNone(conn)
            self.assertRegexpMatches(pgsql.read_bootlog(), 'is ready to accept connections')
            conn.close()
        finally:
            # shutting down
            pid = pgsql.server_pid
            self.assertTrue(pgsql.is_alive())

            pgsql.stop()
            sleep(1)

            self.assertFalse(pgsql.is_alive())
            with self.assertRaises(OSError):
                os.kill(pid, 0)  # process is down

def test_pg8000(self):
        try:
            import pg8000
        except ImportError:
            return
        conn = pg8000.DBAPI.connect(host="127.0.0.1", user="test",
                                    password="test")
        c = conn.cursor()
        c.execute(self.stmt)
        c.fetchone()
        c.close()
        conn.close()
        stats, result = get_local_storage(local_timing).get_thread_stats()
        assert len(result) == 2

def testSocketMissing(self):
        conn_params = {
            'unix_sock': "/file-does-not-exist",
            'user': "doesn't-matter"}
        self.assertRaises(pg8000.InterfaceError, pg8000.connect, **conn_params)

def connect_to_postgres():
            try:
                return connect(host=host, user=user, port=port,
                               database=database)
            except (InterfaceError, ProgrammingError):
                return False