async def test_numeric(db_cfg, event_loop):
db = pgware.build(output='dict', param_format='psycopg2', **db_cfg)
from decimal import Decimal
async with db.get_connection() as pgw:
await pgw.execute("""
CREATE TEMPORARY TABLE pgware_test (
test_int int,
test_dec decimal,
test_num numeric,
test_real real,
test_money money
)
""")
await pgw.execute(
"""
INSERT INTO pgware_test VALUES (
%s,
%s,
%s,
%s,
%s
)
""", (12, 1.2, 1.2, 12, '12'))
result = await pgw.fetchone("SELECT * FROM pgware_test")
assert (result['test_int'] == 12)
assert (float(result['test_dec']) == float(Decimal('1.2')))
assert (float(result['test_num']) == float(Decimal('1.2')))
assert (result['test_real'] == 12)
assert (result['test_money'] == '$12.00')
def test_native_outputs(db_cfg):
pgw = pgware.build(output='native', param_format='postgresql', **db_cfg)
# Both native formats allow access to values by index or by name
with pgw.get_connection() as pgw:
pgw.execute("""
CREATE TEMPORARY TABLE pgware_test (
one varchar(50),
two int,
three boolean
)
""")
pgw.execute("INSERT INTO pgware_test VALUES ('pgware', 2, TRUE)")
result = pgw.fetchone("SELECT * FROM pgware_test")
assert (result[0] == 'pgware')
assert (result[1] == 2)
assert (result[2] is True)
assert (result['one'] == 'pgware')
assert (result['two'] == 2)
assert (result['three'] is True)
result = pgw.fetchall("SELECT * FROM pgware_test")
assert (result[0][0] == 'pgware')
assert (result[0][1] == 2)
assert (result[0][2] is True)
assert (result[0]['one'] == 'pgware')
assert (result[0]['two'] == 2)
assert (result[0]['three'] is True)
result = pgw.fetchval("SELECT * FROM pgware_test")
assert (result == 'pgware')
def test_iterator(db_cfg):
pgw = pgware.build(output='dict', param_format='postgresql', **db_cfg)
# Both native formats allow access to values by index or by name
with pgw.get_connection().cursor() as cur:
cur.execute("""
CREATE TEMPORARY TABLE pgware_test (
one varchar(50),
two int,
three boolean
)
""")
cur.execute("INSERT INTO pgware_test VALUES ('pgware', 2, TRUE)")
cur.execute("INSERT INTO pgware_test VALUES ('pgloop', 3, FALSE)")
cur.fetchall("SELECT * FROM pgware_test")
out = []
for row in cur:
out.append(row)
print(row)
print(out)
assert out[0]['one'] == 'pgware'
assert out[1]['one'] == 'pgloop'
cur.execute("INSERT INTO pgware_test VALUES ('pglimp', 4, FALSE)")
cur.execute("SELECT * FROM pgware_test")
out = []
for row in cur:
out.append(row)
print(row)
assert out[0]['one'] == 'pgware'
assert out[1]['one'] == 'pgloop'
assert out[2]['one'] == 'pglimp'
async def test_dict_outputs(db_cfg, event_loop):
pgw = pgware.build(output='dict', param_format='postgresql', **db_cfg)
async with pgw.get_connection() as pgw:
await pgw.execute("""
CREATE TEMPORARY TABLE pgware_test (
one varchar(50),
two int,
three boolean
)
""")
await pgw.execute("INSERT INTO pgware_test VALUES ('pgware', 2, TRUE)")
result = await pgw.fetchone("SELECT * FROM pgware_test")
assert (isinstance(result, dict))
assert (result['one'] == 'pgware')
assert (result['two'] == 2)
assert (result['three'] is True)
result = await pgw.fetchall("SELECT * FROM pgware_test")
assert (isinstance(result[0], dict))
assert (result[0]['one'] == 'pgware')
assert (result[0]['two'] == 2)
assert (result[0]['three'] is True)
result = await pgw.fetchval("SELECT * FROM pgware_test")
assert (result == 'pgware')
def test_list_outputs(db_cfg):
pgw = pgware.build(output='list', param_format='postgresql', **db_cfg)
with pgw.get_connection() as pgw:
pgw.execute("""
CREATE TEMPORARY TABLE pgware_test (
one varchar(50),
two int,
three boolean
)
""")
pgw.execute("INSERT INTO pgware_test VALUES ('pgware', 2, TRUE)")
result = pgw.fetchone("SELECT * FROM pgware_test")
assert (isinstance(result, list))
assert (result[0] == 'pgware')
assert (result[1] == 2)
assert (result[2] is True)
result = pgw.fetchall("SELECT * FROM pgware_test")
assert (isinstance(result, list))
assert (isinstance(result[0], list))
assert (result[0][0] == 'pgware')
assert (result[0][1] == 2)
assert (result[0][2] is True)
result = pgw.fetchval("SELECT * FROM pgware_test")
assert (result == 'pgware')
async def test_simple(db_cfg, event_loop):
db = pgware.build(output='dict', **db_cfg)
async with db.get_connection() as pgw:
await pgw.execute(
"CREATE TEMPORARY TABLE pgware_test (val varchar(50))")
await pgw.execute("INSERT INTO pgware_test VALUES ('pgware')")
result = await pgw.fetchone("SELECT val as val FROM pgware_test")
assert (result['val'] == 'pgware')
async def main():
print('----------------- TEST ASYNC -----------------------')
print('----------------------------------------------------')
db = pgware.build(output='dict', **config)
# Test all methods in all possible combinations
for i in range(0, 4000):
async with db.connect() as pgw:
await pgw.fetchall(SQL)
def test_json_querying(db_cfg):
pgw = pgware.build(output='dict', param_format='postgresql', **db_cfg)
with pgw.get_connection() as pg:
result = pg.fetchone('select $1::jsonb as json', ({
'sauce': "andalouse"
}, ))
assert ('andalouse' == result['json']['sauce'])
result = pg.fetchone('select $1::jsonb as json', (['andalouse'], ))
assert ('andalouse' == result['json'][0])
async def test_prepared_query_params_postgresql(db_cfg, event_loop):
pgw = pgware.build(output='dict', param_format='postgresql', **db_cfg)
async with pgw.get_connection() as conn:
stmt = await conn.prepare('select $1 as one, $2 as two')
result = await stmt.fetchone(('frites', 'mayo'))
assert ('frites' == result['one'])
assert ('mayo' == result['two'])
result = await stmt.fetchone(('frites', 'ketchup'))
assert ('frites' == result['one'])
assert ('ketchup' == result['two'])
def pgware_psycopg2_test():
global QUERIES
db = pgware.build(type='single',
output='dict',
client='psycopg2',
**psy_config)
with db.connect() as cur:
for _i in range(QUERIES):
cur.execute(SQL.format(i=_i))
cur.fetchone()
def test_asyncpg_syntax(db_cfg):
pgw = pgware.build(output='dict', param_format='postgresql', **db_cfg)
with pgw.get_connection() as pg:
result = pg.fetchone('select $1 as one, $2 as two', ('pouly', 'croc'))
assert ('pouly' == result['one'])
assert ('croc' == result['two'])
result = pg.fetchone('select $2 as one, $1 as two', ('pouly', 'croc'))
assert ('croc' == result['one'])
assert ('pouly' == result['two'])
def test_prepared_query_params_psycopg2(db_cfg):
pgw = pgware.build(output='dict', param_format='psycopg2', **db_cfg)
with pgw.get_connection() as conn:
stmt = conn.prepare('select %s as one, %s as two')
result = stmt.fetchone(('frites', 'mayo'))
assert ('frites' == result['one'])
assert ('mayo' == result['two'])
result = stmt.fetchone(('frites', 'ketchup'))
assert ('frites' == result['one'])
assert ('ketchup' == result['two'])
async def test_complex_psycopg2(db_cfg, event_loop):
db = pgware.build(output='dict',
param_format='psycopg2',
auto_json=False,
**db_cfg)
async with db.get_connection() as pgw:
result = await pgw.fetchone(COMPLEX_QUERY_PSYCOPG2,
COMPLEX_QUERY_VALUES)
print(result)
assert (result['txt'] == COMPLEX_QUERY_VALUES[0])
async def test_query2_error(db_cfg, event_loop):
""" Undefined table """
pgw = pgware.build(output='dict', **db_cfg)
try:
async with pgw.get_connection().cursor() as cur:
await cur.execute('INSERT INTO plouplou VALUES (1,2)')
except PgWareError as ex:
assert isinstance(ex, QueryError)
assert isinstance(ex, PublicError)
else:
assert False, "Exception failed to be raised"
def test_cursor_query(db_cfg):
pgw = pgware.build(output='dict', **db_cfg)
with pgw.get_connection().cursor() as cur:
cur.execute('select 1')
result = cur.fetchone()
assert (1 == result['?column?'])
with pgw.get_connection().cursor() as cur:
cur.execute('select 2')
result = cur.fetchone()
assert (2 == result['?column?'])
async def test_cursor_query_async(db_cfg, event_loop):
pgw = pgware.build(output='dict', **db_cfg)
async with pgw.get_connection().cursor() as cur:
await cur.execute('select 1')
result = await cur.fetchone()
assert (1 == result['?column?'])
async with pgw.get_connection().cursor() as cur:
await cur.execute('select 2')
result = await cur.fetchone()
assert (2 == result['?column?'])
async def pgware_asyncpg_test():
global QUERIES
db = pgware.build(auto_json=False,
type='single',
output='dict',
client='asyncpg',
**asy_config)
async with db.connect() as conn:
for _i in range(QUERIES):
await conn.fetchone(SQL.format(i=_i))
await db.close()
async def test_programming2_error(db_cfg, event_loop):
""" Bad method usage """
pgw = pgware.build(output='dict', **db_cfg)
try:
async with pgw.get_connection() as conn:
await conn.execute(('values', 'without', 'query'))
except PgWareError as ex:
assert isinstance(ex, PublicError)
assert isinstance(ex, ProgrammingError)
else:
assert False, "Exception failed to be raised"
async def test_query_error(db_cfg, event_loop):
""" Syntax error """
pgw = pgware.build(output='dict', **db_cfg)
try:
async with pgw.get_connection().cursor() as cur:
await cur.execute('SELRECT 2')
except PgWareError as ex:
assert isinstance(ex, QueryError)
assert isinstance(ex, PublicError)
else:
assert False, "Exception failed to be raised"
async def test_prepared_cursor_query_params_postgresql(db_cfg, event_loop):
pgw = pgware.build(output='dict', param_format='postgresql', **db_cfg)
async with pgw.get_connection().cursor() as conn:
stmt = await conn.prepare('select $1 as one, $2 as two')
await stmt.execute(('pouly', 'croc'))
result = await stmt.fetchone()
assert ('pouly' == result['one'])
assert ('croc' == result['two'])
await stmt.execute(('mexi', 'canos'))
result = await stmt.fetchone()
assert ('mexi' == result['one'])
assert ('canos' == result['two'])
async def test_retries_exhausted_error(db_cfg, event_loop):
""" Force error by setting wrong host """
db_cfg['host'] = '127.0.0.1'
pgw = pgware.build(output='dict', **db_cfg)
try:
async with pgw.get_connection().cursor() as cur:
await cur.execute('SELECT 2')
except PgWareError as ex:
assert isinstance(ex, PublicError)
assert isinstance(ex, RetriesExhausted)
else:
assert False, "Exception failed to be raised"
async def test_programming2_error(db_cfg, event_loop):
""" Bad config settings"""
db_cfg['client'] = 'samurai'
pgw = pgware.build(output='dict', **db_cfg)
try:
async with pgw.get_connection().cursor() as cur:
await cur.execute(('values', 'without', 'query'))
except PgWareError as ex:
assert isinstance(ex, PublicError)
assert isinstance(ex, ProgrammingError)
else:
assert False, "Exception failed to be raised"
def test_psycogp2_syntax(db_cfg):
pgw = pgware.build(output='dict', param_format='psycopg2', **db_cfg)
with pgw.get_connection() as pg:
result = pg.fetchone('select %s as one, %s as two', ('pouly', 'croc'))
assert ('pouly' == result['one'])
assert ('croc' == result['two'])
with pgw.get_connection() as pg:
result = pg.fetchone('select %(qui)s as one, %(quoi)s as two', {
'quoi': 'pouly',
'qui': 'croc'
})
assert ('croc' == result['one'])
assert ('pouly' == result['two'])
async def test_doodad(event_loop):
db = pgware.build(output='dict', **config)
test = [0] * 4
@doodad
def test0(state):
test[0] += 1
yield state
@doodad
def test1(state):
test[1] += 1
yield state
@doodad
def test2(state):
test[2] += 1
yield state
@doodad
def test3(state):
test[3] += 1
yield state
db.add_doodad('connection', test0)
db.add_doodad('parsing', test1)
async with db.get_connection() as conn:
conn.add_doodad('execution', test2)
await conn.fetchone('select 1')
assert (test[0] == 1)
assert (test[1] == 1)
assert (test[2] == 1)
assert (test[3] == 0)
conn.add_doodad('execution', test3)
await conn.fetchone('select 1')
assert (test[1] == 2)
assert (test[2] == 2)
assert (test[3] == 1)
test[1] = 0
test[2] = 0
test[3] = 0
async with db.get_connection() as conn:
await conn.fetchone('select 1')
assert (test[1] == 1)
assert (test[2] == 0)
assert (test[3] == 0)
async def test_char(db_cfg, event_loop):
db = pgware.build(output='dict', param_format='psycopg2', **db_cfg)
async with db.get_connection() as pgw:
await pgw.execute("""
CREATE TEMPORARY TABLE pgware_test (
test_vchar varchar(10),
test_char char(4),
test_txt text
)
""")
await pgw.execute("INSERT INTO pgware_test VALUES (%s, %s, %s)",
("un", "deux", "trois"))
result = await pgw.fetchone("SELECT * FROM pgware_test")
assert (result['test_vchar'] == "un")
assert (result['test_char'] == "deux")
assert (result['test_txt'] == "trois")
async def test_json(db_cfg, event_loop):
db = pgware.build(output='dict', param_format='psycopg2', **db_cfg)
async with db.get_connection() as pgw:
await pgw.execute("""
CREATE TEMPORARY TABLE pgware_test (
test_json json,
test_jsonb jsonb
)
""")
await pgw.execute("INSERT INTO pgware_test VALUES (%s, %s)",
({
'foo': 'bar'
}, {
'pouly': 'croc'
}))
result = await pgw.fetchone("SELECT * FROM pgware_test")
assert (result['test_json'] == {'foo': 'bar'})
assert (result['test_jsonb'] == {'pouly': 'croc'})
async def test_time(db_cfg, event_loop):
db = pgware.build(output='dict', param_format='psycopg2', **db_cfg)
async with db.get_connection() as pgw:
await pgw.execute("""
CREATE TEMPORARY TABLE pgware_test (
test_tm timestamp without time zone,
test_tmtz timestamp with time zone,
test_date date,
test_time time
)
""")
now = datetime.now()
await pgw.execute("INSERT INTO pgware_test VALUES (%s, %s, %s, %s)",
(now, now, now.date(), now.time()))
result = await pgw.fetchone("SELECT * FROM pgware_test")
assert (result['test_tm'] == now)
assert (result['test_tmtz'].date() == now.date())
assert (result['test_date'] == now.date())
assert (result['test_time'] == now.time())
async def test_arrays(db_cfg, event_loop):
db = pgware.build(output='dict',
param_format='psycopg2',
auto_json=False,
**db_cfg)
import json
async with db.get_connection() as pgw:
await pgw.execute("""
CREATE TEMPORARY TABLE pgware_test (
test_arrr text[],
test_arri integer[],
test_arrj jsonb[]
)
""")
await pgw.execute(
"INSERT INTO pgware_test VALUES (%s, %s, %s::jsonb[])",
(["a", "b", "c"], [1, 2, 3],
[json.dumps({'foo': 'bar'}),
json.dumps({'pouly': 'croc'})]))
result = await pgw.fetchone("SELECT * FROM pgware_test")
assert (result['test_arrr'] == ["a", "b", "c"])
assert (result['test_arri'] == [1, 2, 3])
# Needs more work:
# assert(result['test_arrj'] == [{'foo':'bar'}, {'pouly':'croc'}])
await pgw.execute('TRUNCATE TABLE pgware_test')
await pgw.execute(
"INSERT INTO pgware_test VALUES (%(one)s::text[], %(two)s, %(three)s::jsonb[])",
{
'one': ["a", "b", "c"],
'two': [1, 2, 3],
'three':
[json.dumps({'foo': 'bar'}),
json.dumps({'pouly': 'croc'})]
})
result = await pgw.fetchone("SELECT * FROM pgware_test")
assert (result['test_arrr'] == ["a", "b", "c"])
assert (result['test_arri'] == [1, 2, 3])
def test_query(db_cfg):
pgw = pgware.build(output='dict', **db_cfg)
with pgw.get_connection() as cur:
result = cur.fetchone('select 1')
assert (1 == result['?column?'])
def get_db(app):
if 'pgw' not in g:
g.pgw = pgware.build(client='psycopg2',
auto_json=False,
**app.config['postgresql'])
return g.pgw.get_connection()