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"'
)
