def test_unicode(self, conn): s = sql.SQL(u"select {0} from {1}").format( sql.Identifier(u"field"), sql.Identifier("table") ) s1 = s.as_string(conn) assert isinstance(s1, str) assert s1 == u'select "field" from "table"'
def test_dict(self, conn): s = sql.SQL("select {f} from {t}").format( f=sql.Identifier("field"), t=sql.Identifier("table") ) s1 = s.as_string(conn) assert isinstance(s1, str) assert s1 == 'select "field" from "table"'
def test_repr(self): obj = sql.Identifier("fo'o") assert repr(obj) == 'Identifier("fo\'o")' assert repr(obj) == str(obj) obj = sql.Identifier("fo'o", 'ba"r') assert repr(obj) == "Identifier(\"fo'o\", 'ba\"r')" assert repr(obj) == str(obj)
def test_pos_spec(self, conn): s = sql.SQL("select {0} from {1}").format( sql.Identifier("field"), sql.Identifier("table") ) s1 = s.as_string(conn) assert isinstance(s1, str) assert s1 == 'select "field" from "table"' s = sql.SQL("select {1} from {0}").format( sql.Identifier("table"), sql.Identifier("field") ) s1 = s.as_string(conn) assert isinstance(s1, str) assert s1 == 'select "field" from "table"'
def test_join(self, conn): obj = sql.SQL(", ").join( [sql.Identifier("foo"), sql.SQL("bar"), sql.Literal(42)] ) assert isinstance(obj, sql.Composed) assert obj.as_string(conn) == '"foo", bar, 42' obj = sql.SQL(", ").join( sql.Composed( [sql.Identifier("foo"), sql.SQL("bar"), sql.Literal(42)] ) ) assert isinstance(obj, sql.Composed) assert obj.as_string(conn) == '"foo", bar, 42' obj = sql.SQL(", ").join([]) assert obj == sql.Composed([])
def make_testfunc(conn): # This parameter name tests for injection and quote escaping paramname = """Robert'); drop table "students" --""" procname = "randall" # Set up the temporary function stmt = (sql.SQL(""" create function {}({} numeric) returns numeric language sql as 'select $1 * $1' """).format(sql.Identifier(procname), sql.Identifier(paramname)).as_string(conn).encode( conn.client_encoding)) # execute regardless of sync/async conn conn.pgconn.exec_(stmt) return namedtuple("Thang", "name, param")(procname, paramname)
def __init__(self, connection): self.conn = connection self._format = Format.BINARY self.records = [] self._schema = None self._types_names = None self._makers = {} self.table_name = sql.Identifier("fake_table")
def test_json_load_copy(conn, val, jtype, fmt_out): cur = conn.cursor() stmt = sql.SQL("copy (select {}::{}) to stdout (format {})").format( val, sql.Identifier(jtype), sql.SQL(fmt_out.name) ) with cur.copy(stmt) as copy: copy.set_types([jtype]) (got,) = copy.read_row() assert got == json.loads(val)
def types_names(self): if self._types_names: return self._types_names record = self.make_record(nulls=0) tx = psycopg3.adapt.Transformer(self.conn) types = [] registry = self.conn.adapters.types for value in record: dumper = tx.get_dumper(value, self.format) dumper.dump(value) # load the oid if it's dynamic (e.g. array) info = registry.get(dumper.oid) or registry.get("text") if dumper.oid == info.array_oid: types.append(sql.SQL("{}[]").format(sql.Identifier(info.name))) else: types.append(sql.Identifier(info.name)) self._types_names = types return types
def test_callproc_dict(conn): testfunc = make_testfunc(conn) cur = conn.cursor() cur.callproc(testfunc.name, [2]) assert cur.fetchone() == (4, ) cur.callproc(testfunc.name, {testfunc.param: 2}) assert cur.fetchone() == (4, ) cur.callproc(sql.Identifier(testfunc.name), {testfunc.param: 2}) assert cur.fetchone() == (4, )
async def test_callproc_dict(aconn): testfunc = make_testfunc(aconn) cur = await aconn.cursor() await cur.callproc(testfunc.name, [2]) assert (await cur.fetchone()) == (4,) await cur.callproc(testfunc.name, {testfunc.param: 2}) assert await (cur.fetchone()) == (4,) await cur.callproc(sql.Identifier(testfunc.name), {testfunc.param: 2}) assert await (cur.fetchone()) == (4,)
def test_copy(self, conn): cur = conn.cursor() cur.execute( """ create table test_compose ( id serial primary key, foo text, bar text, "ba'z" text) """ ) with cur.copy( sql.SQL("copy {t} (id, foo, bar, {f}) from stdin").format( t=sql.Identifier("test_compose"), f=sql.Identifier("ba'z") ), ) as copy: copy.write_row((10, "a", "b", "c")) copy.write_row((20, "d", "e", "f")) with cur.copy( sql.SQL("copy (select {f} from {t} order by id) to stdout").format( t=sql.Identifier("test_compose"), f=sql.Identifier("ba'z") ) ) as copy: assert list(copy) == [b"c\n", b"f\n"]
def test_init(self): assert isinstance(sql.Identifier("foo"), sql.Identifier) assert isinstance(sql.Identifier(u"foo"), sql.Identifier) assert isinstance(sql.Identifier("foo", "bar", "baz"), sql.Identifier) with pytest.raises(TypeError): sql.Identifier() with pytest.raises(TypeError): sql.Identifier(10) with pytest.raises(TypeError): sql.Identifier(dt.date(2016, 12, 31))
def test_executemany(self, conn): cur = conn.cursor() cur.execute( """ create table test_compose ( id serial primary key, foo text, bar text, "ba'z" text) """ ) cur.executemany( sql.SQL("insert into {0} (id, {1}) values (%s, {2})").format( sql.Identifier("test_compose"), sql.SQL(", ").join( map(sql.Identifier, ["foo", "bar", "ba'z"]) ), (sql.Placeholder() * 3).join(", "), ), [(10, "a", "b", "c"), (20, "d", "e", "f")], ) cur.execute("select * from test_compose") assert cur.fetchall() == [(10, "a", "b", "c"), (20, "d", "e", "f")]
def test_eq(self): assert sql.Identifier("foo") == sql.Identifier("foo") assert sql.Identifier("foo", "bar") == sql.Identifier("foo", "bar") assert sql.Identifier("foo") != sql.Identifier("bar") assert sql.Identifier("foo") != "foo" assert sql.Identifier("foo") != sql.SQL("foo")
def fields_names(self): return [sql.Identifier(f"fld_{i}") for i in range(len(self.schema))]
def test_join(self, conn): obj = sql.Composed([sql.Literal("foo"), sql.Identifier("b'ar")]) obj = obj.join(", ") assert isinstance(obj, sql.Composed) assert noe(obj.as_string(conn)) == "'foo', \"b'ar\""
def test_eq(self): L = [sql.Literal("foo"), sql.Identifier("b'ar")] l2 = [sql.Literal("foo"), sql.Literal("b'ar")] assert sql.Composed(L) == sql.Composed(list(L)) assert sql.Composed(L) != L assert sql.Composed(L) != sql.Composed(l2)
def test_repr(self): obj = sql.Composed([sql.Literal("foo"), sql.Identifier("b'ar")]) assert ( repr(obj) == """Composed([Literal('foo'), Identifier("b'ar")])""" ) assert str(obj) == repr(obj)
def test_join(self): assert not hasattr(sql.Identifier("foo"), "join")
def test_as_str(self, conn): assert sql.Identifier("foo").as_string(conn) == '"foo"' assert sql.Identifier("foo", "bar").as_string(conn) == '"foo"."bar"' assert ( sql.Identifier("fo'o", 'ba"r').as_string(conn) == '"fo\'o"."ba""r"' )