Python psycopg2.DataError() Examples

def insert_rows(self, rows, table_name=None):
        """
        Inserts many rows, all in the same transaction, using psycopg2.extras.execute_values
        """

        if table_name is None:
            table_name = self.table_name

        with self.conn.cursor() as curs:
            sql_str, template = sql.insert_many(table_name, rows)
            try:
                psycopg2.extras.execute_values(
                    curs,
                    sql_str,
                    rows,
                    template=template,
                    page_size=len(rows)
                )
            except psycopg2.DataError:
                print(rows) # useful for debugging
                raise
        self.conn.commit() 

def sentences(self):
        """ Return a list of all test sentences in the database """
        assert self._c is not None
        m = [ ]
        try:
            self._c.execute("SELECT id, sentence, numtrees, best, target FROM sentences ORDER BY id;")
            t = self._c.fetchall()
            m = [ dict(
                    identity = r[0],
                    sentence = r[1],
                    numtrees = r[2],
                    best = r[3],
                    target = r[4])
                for r in t]
        except psycopg2.DataError as e:
            # Fall through with empty m
            pass
        return m 

def forms(w):
        """ Return a list of all possible forms of a particular root (stem) """
        c = Meanings._cursor
        assert c is not None
        m = None
        try:
            c.execute(Meanings._DB_Q_FORMS, [w])
            # Map the returned data from fetchall() to a list of instances
            # of the BIN_Meaning namedtuple
            g = c.fetchall()
            if g is not None:
                m = list(map(BIN_Meaning._make, g))
        except (psycopg2.DataError, psycopg2.ProgrammingError) as e:
            print("Word '{0}' caused DB exception {1}".format(w, e))
            m = None
        return m 

def test_begin_nested(engine):
    async with engine.cursor() as cur:
        async with cur.begin_nested():
            await cur.execute("insert into tbl values(1, 'data')")

            with pytest.raises(psycopg2.DataError):
                async with cur.begin_nested():
                    await cur.execute("insert into tbl values(1/0, 'no data')")

            async with cur.begin_nested():
                await cur.execute("insert into tbl values(2, 'data')")

        async with cur.begin_nested():
            await cur.execute('select * from tbl')
            row = await cur.fetchall()
            assert row == [(22, 'read only'), (1, 'data'),
                           (2, 'data'), ] 

def test_transaction_point(engine):
    async with engine.cursor() as cur:
        async with Transaction(cur, IsolationLevel.read_committed) as tr:
            await cur.execute("insert into tbl values(1, 'data')")

            with pytest.raises(psycopg2.DataError):
                async with tr.point():
                    await cur.execute("insert into tbl values(1/0, 'data')")

            async with tr.point():
                await cur.execute("insert into tbl values(2, 'data point')")

            await cur.execute("insert into tbl values(3, 'data')")

            await cur.execute('select * from tbl')
            row = await cur.fetchall()
            assert row == [(22, 'read only'), (1, 'data'),
                           (2, 'data point'), (3, 'data')] 

def test_transaction_point_oldstyle(engine):
    async with engine.acquire() as cur:
        tr = Transaction(cur, IsolationLevel.read_committed)
        await tr.begin()

        await cur.execute("insert into tbl values(1, 'data')")

        try:
            await tr.savepoint()
            await cur.execute("insert into tbl values(1/0, 'no data')")
        except psycopg2.DataError:
            await tr.rollback_savepoint()

        await tr.savepoint()
        await cur.execute("insert into tbl values(2, 'data')")
        await tr.release_savepoint()

        await cur.execute("insert into tbl values(3, 'data')")

        resp = await cur.execute('select * from tbl')
        row = await resp.fetchall()
        assert row == [(22, 'read only'), (1, 'data'), (2, 'data'),
                       (3, 'data')]

        await tr.commit() 

def test_wrong_schema(self):
        oid = self._create_type("type_ii", [("a", "integer"), ("b", "integer")])
        from psycopg2.extras import CompositeCaster
        c = CompositeCaster('type_ii', oid, [('a', 23), ('b', 23), ('c', 23)])
        curs = self.conn.cursor()
        psycopg2.extensions.register_type(c.typecaster, curs)
        curs.execute("select (1,2)::type_ii")
        self.assertRaises(psycopg2.DataError, curs.fetchone) 

def log_chat(self, sender, message, channel):
        if len(message) > 512:
            message = message[:512]

        if self.cursor.closed:
            return

        try:
            self.cursor.execute("INSERT INTO chat_log (sender, message, channel, date) VALUES (%s, %s, %s, %s)",
                                (sender, message, channel, current_time_in_milli()))
        except psycopg2.DataError as e:
            print e
            print message 

def test_begin_nested_fail(engine):
    async with engine.cursor() as cur:
        with pytest.raises(psycopg2.DataError):
            async with cur.begin_nested():
                await cur.execute("insert into tbl values(1/0, 'data')")

        async with cur.begin_nested():
            await cur.execute('select * from tbl')
            row = await cur.fetchall()
            assert row == [(22, 'read only')] 

def test_transaction_rollback(engine):
    async with engine.cursor() as cur:
        with pytest.raises(psycopg2.DataError):
            async with Transaction(cur, IsolationLevel.read_committed):
                await cur.execute("insert into tbl values(1/0, 'no data')")

        await cur.execute('select * from tbl')
        row = await cur.fetchall()
        assert row == [(22, 'read only'), ] 

def parse(self, s, curs):
        if s is None:
            return None

        tokens = self.tokenize(s)
        if len(tokens) != len(self.atttypes):
            raise psycopg2.DataError(
                "expecting %d components for the type %s, %d found instead" %
                (len(self.atttypes), self.name, len(tokens)))

        values = [ curs.cast(oid, token)
            for oid, token in zip(self.atttypes, tokens) ]

        return self.make(values) 

def test_copy_rowcount_error(self):
        curs = self.conn.cursor()

        curs.execute("insert into tcopy (data) values ('fff')")
        self.assertEqual(curs.rowcount, 1)

        self.assertRaises(psycopg2.DataError,
            curs.copy_from, StringIO('aaa\nbbb\nccc\n'), 'tcopy')
        self.assertEqual(curs.rowcount, -1) 

def vfolder_check_exists(handler: Callable[..., Awaitable[web.Response]]):
    '''
    Checks if the target vfolder exists and is owned by the current user.

    The decorated handler should accept an extra "row" argument
    which contains the matched VirtualFolder table row.
    '''

    @functools.wraps(handler)
    async def _wrapped(request: web.Request, *args, **kwargs) -> web.Response:
        dbpool = request.app['dbpool']
        user_uuid = request['user']['uuid']
        folder_name = request.match_info['name']
        async with dbpool.acquire() as conn:
            j = sa.join(
                vfolders, vfolder_permissions,
                vfolders.c.id == vfolder_permissions.c.vfolder, isouter=True)
            query = (
                sa.select('*')
                .select_from(j)
                .where(((vfolders.c.user == user_uuid) |
                        (vfolder_permissions.c.user == user_uuid)) &
                       (vfolders.c.name == folder_name)))
            try:
                result = await conn.execute(query)
            except psycopg2.DataError:
                raise InvalidAPIParameters
            row = await result.first()
            if row is None:
                raise VFolderNotFound()
        return await handler(request, row, *args, **kwargs)

    return _wrapped 

def test_exception_swallow(self):
        # bug #262: __exit__ calls cur.close() that hides the exception
        # with another error.
        try:
            with self.conn as conn:
                with conn.cursor('named') as cur:
                    cur.execute("select 1/0")
                    cur.fetchone()
        except psycopg2.DataError, e:
            self.assertEqual(e.pgcode, '22012') 

def testArrayMalformed(self):
        curs = self.conn.cursor()
        ss = ['', '{', '{}}', '{' * 20 + '}' * 20]
        for s in ss:
            self.assertRaises(psycopg2.DataError,
                psycopg2.extensions.STRINGARRAY, b(s), curs) 

def testFloatInf(self):
        try:
            self.execute("select 'inf'::float")
        except psycopg2.DataError:
            return self.skipTest("inf::float not available on the server")
        except ValueError:
            return self.skipTest("inf not available on this platform")
        s = self.execute("SELECT %s AS foo", (float("inf"),))
        self.failUnless(str(s) == "inf", "wrong float quoting: " + str(s))      
        self.failUnless(type(s) == float, "wrong float conversion: " + repr(s))

        s = self.execute("SELECT %s AS foo", (float("-inf"),))
        self.failUnless(str(s) == "-inf", "wrong float quoting: " + str(s)) 

def test_parse_incomplete_datetime(self):
        self.assertRaises(psycopg2.DataError,
                          self.DATETIME, '2007', self.curs)
        self.assertRaises(psycopg2.DataError,
                          self.DATETIME, '2007-01', self.curs)
        self.assertRaises(psycopg2.DataError,
                          self.DATETIME, '2007-01-01 13', self.curs)
        self.assertRaises(psycopg2.DataError,
                          self.DATETIME, '2007-01-01 13:30', self.curs) 

def test_parse_incomplete_time(self):
        self.assertRaises(psycopg2.DataError, self.TIME, '13', self.curs)
        self.assertRaises(psycopg2.DataError, self.TIME, '13:30', self.curs) 

def test_parse_incomplete_date(self):
        self.assertRaises(psycopg2.DataError, self.DATE, '2007', self.curs)
        self.assertRaises(psycopg2.DataError, self.DATE, '2007-01', self.curs) 

def test_insertErr(self):
		key = len(TEST_DATA) + 2
		self.assertRaises((ValueError, psycopg2.DataError), self.tree.insert, 'wat', 1) 

def test_path_invalid_repo_name_and_job_name(self):
        try:
            self.get('/v2/hello/world', self.get_project_headers())
        except psycopg2.DataError:
            pass 

def test_path_invalid_repo_name_and_job_name(self):
        try:
            self.get('/v2/hello/world', self.get_project_headers())
        except psycopg2.DataError:
            pass 

def parse(self, s, curs):
        if s is None:
            return None

        tokens = self.tokenize(s)
        if len(tokens) != len(self.atttypes):
            raise psycopg2.DataError(
                "expecting %d components for the type %s, %d found instead" %
                (len(self.atttypes), self.name, len(tokens)))

        values = [curs.cast(oid, token)
            for oid, token in zip(self.atttypes, tokens)]

        return self.make(values) 

def test_range_escaping(self):
        from psycopg2.extras import register_range
        cur = self.conn.cursor()
        cur.execute("create type textrange as range (subtype=text)")
        rc = register_range('textrange', 'TextRange', cur)

        TextRange = rc.range
        cur.execute("""
            create table rangetest (
                id integer primary key,
                range textrange)""")

        bounds = [ '[)', '(]', '()', '[]' ]
        ranges = [ TextRange(low, up, bounds[i % 4])
            for i, (low, up) in enumerate(zip(
                [None] + map(chr, range(1, 128)),
                map(chr, range(1,128)) + [None],
                ))]
        ranges.append(TextRange())
        ranges.append(TextRange(empty=True))

        errs = 0
        for i, r in enumerate(ranges):
            # not all the ranges make sense:
            # fun fact: select ascii('#') < ascii('$'), '#' < '$'
            # yelds... t, f! At least in en_GB.UTF-8 collation.
            # which seems suggesting a supremacy of the pound on the dollar.
            # So some of these ranges will fail to insert. Be prepared but...
            try:
                cur.execute("""
                    savepoint x;
                    insert into rangetest (id, range) values (%s, %s);
                    """, (i, r))
            except psycopg2.DataError:
                errs += 1
                cur.execute("rollback to savepoint x;")

        # ...not too many errors! in the above collate there are 17 errors:
        # assume in other collates we won't find more than 30
        self.assert_(errs < 30,
            "too many collate errors. Is the test working?")

        cur.execute("select id, range from rangetest order by id")
        for i, r in cur:
            self.assertEqual(ranges[i].lower, r.lower)
            self.assertEqual(ranges[i].upper, r.upper)
            self.assertEqual(ranges[i].lower_inc, r.lower_inc)
            self.assertEqual(ranges[i].upper_inc, r.upper_inc)
            self.assertEqual(ranges[i].lower_inf, r.lower_inf)
            self.assertEqual(ranges[i].upper_inf, r.upper_inf)

        # clear the adapters to allow precise count by scripts/refcounter.py
        del ext.adapters[TextRange, ext.ISQLQuote] 

def setup_postgresql(username, password, host, port):
    """
    https://github.com/my8100/notes/blob/master/back_end/the-flask-mega-tutorial.md
    When working with database servers such as MySQL and PostgreSQL,
    you have to create the database in the database server before running upgrade.
    """
    require_version = '2.7.7'  # Jan 23, 2019
    install_command = "pip install --upgrade psycopg2"
    try:
        import psycopg2
        assert psycopg2.__version__ >= require_version, install_command
    except (ImportError, AssertionError):
        sys.exit("Run command: %s" % install_command)

    conn = psycopg2.connect(host=host, port=int(port), user=username, password=password)
    conn.set_isolation_level(0)  # https://wiki.postgresql.org/wiki/Psycopg2_Tutorial
    cur = conn.cursor()
    for dbname in DBS:
        if SCRAPYDWEB_TESTMODE:
            # database "scrapydweb_apscheduler" is being accessed by other users
            # DETAIL:  There is 1 other session using the database.
            # To restart postgres server on Windonws -> win+R: services.msc
            drop_database(cur, dbname)

        # https://www.postgresql.org/docs/9.0/sql-createdatabase.html
        # https://stackoverflow.com/questions/9961795/
        # utf8-postgresql-create-database-like-mysql-including-character-set-encoding-a

        # psycopg2.ProgrammingError: invalid locale name: "en_US.UTF-8"
        # https://stackoverflow.com/questions/40673339/
        # creating-utf-8-database-in-postgresql-on-windows10

        # cur.execute("CREATE DATABASE %s ENCODING 'UTF8' LC_COLLATE 'en-US' LC_CTYPE 'en-US'" % dbname)
        # psycopg2.DataError: new collation (en-US) is incompatible with the collation of the template database
        # (Chinese (Simplified)_People's Republic of China.936)
        # HINT:  Use the same collation as in the template database, or use template0 as template.
        try:
            cur.execute("CREATE DATABASE %s ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8'" % dbname)
        except:
            try:
                cur.execute("CREATE DATABASE %s" % dbname)
            except Exception as err:
                # psycopg2.ProgrammingError: database "scrapydweb_apscheduler" already exists
                if 'exists' in str(err):
                    pass
                else:
                    raise
    cur.close()
    conn.close()