def test_where(self):
play = Play()
c = Chess()
q = Query("postgres", unsafe=True)
prep = q.SELECT(play, c.name).FROM(play, c).WHERE(c.name == NAME)
self.assertEqual(f"{SELECT_STATEMENT} {FROM}{_CHESS} {WHERE}",
prep.print())
def test_base_equality(self):
h = Holder()
num = 24
sql = f"first_name = {num}"
# self.assertEqual(sql, h.first_name == num)
base = Base()
o = h.first_name == 24
self.assertIsInstance(o, Base)
self.assertEqual(sql, o.print(Query("postgres", unsafe=True)))
from supersql import Query
from supersql import Table
from supersql import Integer, UUID
from supersql import Decimal
from supersql import String
CONFIG = {"vendor": "postgres", "user": "******", "password": "******"}
SQL = """CREATE TABLE account (
\tidentifier uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v1(),
\tfirst_name varchar(25),
\tlast_name varchar
)"""
query = Query(**CONFIG)
class Account(Table):
identifier = UUID(default="uuid_generate_v1()", pk=True, required=True)
first_name = String(25)
last_name = String()
def test_create_table():
account = Account()
assert (query.CREATE(Account, safe=False).print() == SQL)
def test_vendor_required(self):
with self.assertRaises(TypeError):
q = Query()
def test_unsupported(self):
with self.assertRaises(NotImplementedError):
q = Query("mongodb")
def test_supported(self):
for vendor in SUPPORTED_VENDORS:
q = Query(vendor)
self.assertIsInstance(q, Query)
def setUp(self):
self.q = Query("postgres")
self.p = Play()
self.c = Chess()
from supersql import Table, Query
from supersql import String
q = Query(vendor="postgres")
first_name = "first_name"
last_name = "last_name"
tablename = "customers"
primary_key = "identifier"
identifier = 511
class Customers(Table):
identifier = String()
first_name = String()
last_name = String()
def test_from():
expected = "SELECT first_name, last_name FROM customers WHERE identifier = 511"
assert expected == q.SELECT(first_name, last_name).FROM(tablename).WHERE(
f"{primary_key} = {identifier}").print()
def test_delete():
expected = "DELETE FROM customer WHERE identifier = 5"
assert expected == q.DELETE("customer").WHERE("identifier = 5").print()
def test_delete_with_datatype():
from routerling import Router, HttpRequest, ResponseWriter, Context
from ujson import dumps, loads
from supersql import Query, Table, String
query = Query('postgres',
user="******",
password="******",
database="supersql")
router = Router()
class Customer(Table):
name = String()
async def selector(r, w, c):
customer = Customer()
results = await query.SELECT().FROM('customers').WHERE(
customer.name == 'Kiki').run()
if not (bool(results)):
w.status = 404
return
row = results.row(1)
print(len(results._rows))
w.body = f"{row.column('name')}, {row.title}"
async def editor(r: HttpRequest, w: ResponseWriter, c: Context):
jsondata = loads(r.body)
q = query.UPDATE('customers').SET(f"title = '{jsondata.get('title')}'"
from supersql import Query
from .schemas.actor import Actor
from .schemas.staff import Staff
query = Query(user='******',
password='******',
vendor='postrgres',
host='localhost:5432/mydatabase')
actor = Actor()
prep = query.SELECT(actor.actor_id, actor.first_name).FROM(actor).WHERE(
actor.last_name == 'Connery').OFFSET(5).FETCH(10)
fetch_select = """
WITH
cohort_data AS (
SELECT sk_customer,
iso_country_code country,
FIRST(sk_order) OVER (PARTITION BY sk_customer ORDER BY order_date) sk_first_order,
FIRST(order_date) OVER (PARTITION BY sk_customer ORDER BY order_date) first_order_date
FROM f_salesorder_position sale
LEFT JOIN d_shop shop
ON sale.sk_shop = shop.sk_shop
WHERE (sale.sk_order_date BETWEEN {cohort_start} AND {cohort_end}) AND
sales_channel = 'SHOP'
),
cohort_data2 AS (
SELECT sk_customer,
def test_engine_required(self):
with self.assertRaises(TypeError):
q = Query()
def test_supported(self):
for engine in SUPPORTED_ENGINES[:3]:
q = Query(engine)
self.assertIsInstance(q, Query)