def test_insert_from_columns(self):
query = Query.into(self.table_abc).from_(self.table_efg).select(
self.table_efg.fiz, self.table_efg.buz, self.table_efg.baz
)
self.assertEqual('INSERT INTO "abc" '
'SELECT "fiz","buz","baz" FROM "efg"', str(query))
def test_insert_columns_from_star(self):
query = Query.into(self.table_abc).columns(
self.table_abc.foo, self.table_abc.bar, self.table_abc.buz,
).from_(self.table_efg).select('*')
self.assertEqual('INSERT INTO "abc" ("foo","bar","buz") '
'SELECT * FROM "efg"', str(query))
def test_insert_all_columns_multi_rows_chained_mixed(self):
query = Query.into(self.table_abc).insert(
(1, 'a', True), (2, 'b', False)
).insert(3, 'c', True)
self.assertEqual('INSERT INTO "abc" VALUES '
'(1,\'a\',true),(2,\'b\',false),'
'(3,\'c\',true)', str(query))
def test_join_table_on_insert_query(self):
a, b, c = Tables('a', 'b', 'c')
q = Query.into(c).from_(a) \
.join(b).on(a.fkey_id == b.id) \
.where(b.foo == 1) \
.select('a.*', 'b.*')
self.assertEqual('INSERT INTO "c" '
'SELECT "a"."a.*","a"."b.*" '
'FROM "a" JOIN "b" '
'ON "a"."fkey_id"="b"."id" '
'WHERE "b"."foo"=1', str(q))
def test_insert_subquery_wrapped_in_brackets(self):
purchase_order_item, part = Tables("purchase_order_item", "part")
q = Query.into(purchase_order_item) \
.columns(purchase_order_item.id_part, purchase_order_item.id_customer) \
.insert(Query.from_(part)
.select(part.part_id)
.where(part.part_number == "FOOBAR"), 12345)
self.assertEqual('INSERT INTO "purchase_order_item" '
'("id_part","id_customer") '
'VALUES '
'((SELECT "part_id" FROM "part" WHERE "part_number"=\'FOOBAR\'),12345)', str(q))
def test_insert_columns_from_columns_with_join(self):
query = Query.into(self.table_abc).columns(
self.table_abc.c1, self.table_abc.c2, self.table_abc.c3, self.table_abc.c4
).from_(self.table_efg).select(
self.table_efg.foo, self.table_efg.bar
).join(self.table_hij).on(
self.table_efg.id == self.table_hij.abc_id
).select(
self.table_hij.fiz, self.table_hij.buz
)
self.assertEqual('INSERT INTO "abc" ("c1","c2","c3","c4") '
'SELECT "efg"."foo","efg"."bar","hij"."fiz","hij"."buz" FROM "efg" '
'JOIN "hij" ON "efg"."id"="hij"."abc_id"', str(query))
def update_or_add(self, **kwargs):
c = self.connection.cursor()
try:
# Try to add
q = Query.into(self.table)\
.columns(*kwargs.keys())\
.insert(*kwargs.values())
c.execute(q.get_sql())
# TODO: get id of created item
except sqlite3.IntegrityError:
# If the key is a duplicate
# then update
q = Query.update(self.table).where(self.table.id == kwargs['id'])
for key, value in kwargs.items():
if key != id:
q = q.set(key, value)
c.execute(q.get_sql())
self.connection.commit()
# TODO: return legit data instead of kwargs
return kwargs
def input_into_orders_table(input_list):
with portal() as conn:
with conn.cursor() as cur:
try:
customers = Table('lab_orders_table')
q = Query.into(customers).columns(
'item_name', 'catalogue_number', 'cas_number',
'manufacturer', 'supplier', 'price', 'unit_quantity',
'amount_ordered', 'end_user',
'order_reference', 'order_status', 'notes').insert(
input_list[0], input_list[1], input_list[2],
input_list[3], input_list[4], input_list[5],
input_list[6], input_list[7], input_list[8],
input_list[9], input_list[10], input_list[11])
cur.execute(str(q))
except (Exception, psycopg2.DatabaseError) as error:
print('error in db_methods.py-connect_to_database()-function')
print(error)
cur.close()
def test_insert_columns_from_columns_with_join(self):
query = (
Query.into(self.table_abc)
.columns(
self.table_abc.c1,
self.table_abc.c2,
self.table_abc.c3,
self.table_abc.c4,
)
.from_(self.table_efg)
.select(self.table_efg.foo, self.table_efg.bar)
.join(self.table_hij)
.on(self.table_efg.id == self.table_hij.abc_id)
.select(self.table_hij.fiz, self.table_hij.buz)
)
self.assertEqual(
'INSERT INTO "abc" ("c1","c2","c3","c4") '
'SELECT "efg"."foo","efg"."bar","hij"."fiz","hij"."buz" FROM "efg" '
'JOIN "hij" ON "efg"."id"="hij"."abc_id"',
str(query),
)
def test_insert_single_row_with_array_value(self):
query = Query.into(self.table_abc).insert(1, ['a', 'b', 'c'])
self.assertEqual('INSERT INTO "abc" VALUES (1,[\'a\',\'b\',\'c\'])', str(query))
def test_insert_star(self):
query = Query.into(self.table_abc).from_(self.table_efg).select('*')
self.assertEqual('INSERT INTO "abc" SELECT * FROM "efg"', str(query))
def test_insert_all_columns_multi_rows_arrays(self):
query = Query.into(self.table_abc).insert((1, 'a', True), (2, 'b', False))
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true),(2,\'b\',false)', str(query))
def test_replace_simple(self):
query = Query.into(self.table_abc).replace('v1', 'v2', 'v3')
expected_output = 'REPLACE INTO "abc" VALUES (\'v1\',\'v2\',\'v3\')'
self.assertEqual(str(query), expected_output)
def test_replace_insert_table_current_table_not_match(self):
query = Query.into(self.table_a).insert(1)
query = query.replace_table(self.table_c, self.table_b)
self.assertEqual('INSERT INTO "a" VALUES (1)', str(query))
def store_values(self, values):
q = Query.into(values_table)\
.columns(*values_columns)\
.insert(*values)
print(str(q))
return self.execute_statement(str(q))
def register(name, pwd):
usr = Table(cfg.usr)
now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
q = Query.into(usr).columns(usr.name, usr.pwd, usr.created,
usr.updated).insert(name, pwd, now, now)
return q
def test_insert_multiple_rows_with_array_value(self):
query = Query.into(self.table_abc).insert((1, ['a', 'b', 'c']),
(2, ['c', 'd', 'e']), )
self.assertEqual('INSERT INTO "abc" '
'VALUES (1,[\'a\',\'b\',\'c\']),(2,[\'c\',\'d\',\'e\'])', str(query))
def add(self, **kwargs): c = self.connection.cursor() q = Query.into(self.table).columns(*kwargs.keys()).insert(*kwargs.values()) c.execute(q.get_sql()) self.connection.commit() return kwargs
def test_insert_ignore(self):
query = Query.into(self.table_abc).insert(1).ignore()
self.assertEqual('INSERT IGNORE INTO "abc" VALUES (1)', str(query))
def test_insert_null(self):
query = Query.into(self.table_abc).insert(None)
self.assertEqual('INSERT INTO "abc" VALUES (NULL)', str(query))
def test_insert_one_column(self):
query = Query.into(self.table_abc).insert(1)
self.assertEqual('INSERT INTO "abc" VALUES (1)', str(query))
def test_insert_ignore_star(self):
query = Query.into(self.table_abc).from_(self.table_efg).select('*').ignore()
self.assertEqual('INSERT IGNORE INTO "abc" SELECT * FROM "efg"', str(query))
def test_insert_all_columns_single_element(self):
query = Query.into(self.table_abc).insert((1, 'a', True))
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true)', str(query))
def test_param_insert(self):
q = Query.into(self.table_abc).columns("a", "b", "c").insert(
Parameter("?"), Parameter("?"), Parameter("?"))
self.assertEqual('INSERT INTO "abc" ("a","b","c") VALUES (?,?,?)',
q.get_sql())
def test_insert_all_columns_multi_rows(self):
query = Query.into(self.table_abc).insert((1, 'a', True), (2, 'b', False))
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true),(2,\'b\',false)', str(query))
def insert_distance(con, model):
statement = Query.into(DISTANCES).columns('distance', 'timestamp').insert(
model.distance, model.timestamp).get_sql()
con.cursor().execute(statement)
con.commit()
def test_insert_none_skipped(self):
query = Query.into(self.table_abc).insert()
self.assertEqual("", str(query))
def test_replace_insert_table(self):
query = Query.into(self.table_a).insert(1)
query = query.replace_table(self.table_a, self.table_b)
self.assertEqual('INSERT INTO "b" VALUES (1)', str(query))
def test_insert_null(self):
query = Query.into(self.table_abc).insert(None)
self.assertEqual('INSERT INTO "abc" VALUES (NULL)', str(query))
def test_insert_single_row_with_array_value(self):
query = Query.into(self.table_abc).insert(1, ['a', 'b', 'c'])
self.assertEqual('INSERT INTO "abc" VALUES (1,[\'a\',\'b\',\'c\'])',
str(query))
def test_insert_one_column_multi_element_array(self):
query = Query.into(self.table_abc).insert((1, ), (2, ))
self.assertEqual('INSERT INTO "abc" VALUES (1),(2)', str(query))
def test_insert_all_columns_single_element_arrays(self):
query = Query.into(self.table_abc).insert((1, 'a', True))
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true)', str(query))
def test_insert_all_columns_single_element(self):
query = Query.into(self.table_abc).insert((1, "a", True))
self.assertEqual("INSERT INTO \"abc\" VALUES (1,'a',true)", str(query))
def test_insert_selected_columns(self):
query = Query.into(self.table_abc).columns(
self.table_abc.foo, self.table_abc.bar, self.table_abc.buz
).insert(1, 'a', True)
self.assertEqual('INSERT INTO "abc" ("foo","bar","buz") VALUES (1,\'a\',true)', str(query))
def test_replace_simple(self):
query = Query.into(self.table_abc).replace("v1", "v2", "v3")
expected_output = "REPLACE INTO \"abc\" VALUES ('v1','v2','v3')"
self.assertEqual(str(query), expected_output)
def test_insert_empty_columns_type_tuple(self):
query = (Query.into(self.table_abc).columns(()).insert(1, "a", True))
self.assertEqual('INSERT INTO "abc" VALUES (1,\'a\',true)', str(query))
def test_insert_one_column_multi_element_array(self):
query = Query.into(self.table_abc).insert((1,), (2,))
self.assertEqual('INSERT INTO "abc" VALUES (1),(2)', str(query))
def test_insert_ignore(self):
query = Query.into(self.table_abc).insert(1).ignore()
self.assertEqual('INSERT IGNORE INTO "abc" VALUES (1)', str(query))
def test_insert_selected_columns(self):
query = Query.into(self.table_abc).columns(
self.table_abc.foo, self.table_abc.bar, self.table_abc.buz
).insert(1, 'a', True)
self.assertEqual('INSERT INTO "abc" ("foo","bar","buz") VALUES (1,\'a\',true)', str(query))
def test_insert_one_column(self):
query = Query.into(self.table_abc).insert(1)
self.assertEqual('INSERT INTO "abc" VALUES (1)', str(query))
def test_insert_none_skipped(self):
query = Query.into(self.table_abc).insert()
self.assertEqual('', str(query))
def test_insert_single_row_with_array_value(self):
query = Query.into(self.table_abc).insert(1, ["a", "b", "c"])
self.assertEqual("INSERT INTO \"abc\" VALUES (1,['a','b','c'])",
str(query))
def _prepare_insert_statement(self, instance,
db_columns: List[str]) -> str:
return str(
Query.into(Table(
instance.model_meta.db_table)).columns(*db_columns).insert(
*[Parameter("?") for _ in range(len(db_columns))]))
def test_insert_ignore_star(self):
query = Query.into(self.table_abc).from_(
self.table_efg).select("*").ignore()
self.assertEqual('INSERT IGNORE INTO "abc" SELECT * FROM "efg"',
str(query))
def postByUsername(request):
if request.method == 'POST':
try:
jsonRequestData = json.loads(request.body)
except:
return JsonResponse(
{
'msg': 'No post data in request',
'status': 400
}, status=400)
connection = psycopg2.connect(DATABASE_URL, sslmode='require')
if not 'language' in jsonRequestData:
return JsonResponse(
{
'msg': 'Missing language from request',
'status': 400
},
status=400)
elif not 'username' in jsonRequestData:
return JsonResponse(
{
'msg': 'Missing username from request',
'status': 400
},
status=400)
try:
cursor = connection.cursor()
users = Table('ufbe_users')
postUser = Query.into(users).columns(
'username', 'language').insert(jsonRequestData['username'],
jsonRequestData['language'])
try:
cursor.execute(str(postUser))
except (Exception, psycopg2.IntegrityError) as error:
cursor.execute("ROLLBACK;")
if error.pgcode == '23505':
return JsonResponse(
{
'msg': 'User already exists',
'status': 400
},
status=400)
else:
cursor.execute("COMMIT;")
return selectUserByUsername(request,
jsonRequestData['username'])
except (Exception, psycopg2.Error) as error:
print('Error occured ---->', error)
if hasattr(error, 'pgerror'):
errorLines = re.findall(r"[^\n]+\n", error.pgerror)
return JsonResponse(
{
'error': {
'code': error.pgcode,
'msg': errorLines[0][:-1]
}
},
status=400)
else:
return JsonResponse({'error': 'some error'}, status=400)
finally:
if (connection):
cursor.close()
connection.close()
else:
return badMethodJson
def test_replace_subquery(self):
query = Query.into(self.table_abc).replace(
Query.from_(self.table_def).select("f1", "f2"))
expected_output = 'REPLACE INTO "abc" VALUES ((SELECT "f1","f2" FROM "efg"))'
self.assertEqual(str(query), expected_output)
def insert_into(self, table_name, **values):
values = [(col, val) for col, val in values.items()]
q = (Query.into(table_name)
.columns(*(t[0] for t in values))
.insert(*_pypika_params(len(values))))
return self._execute(q, tuple(t[1] for t in values))
