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_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_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))