python-relations-sql
Module for interacting with Relations and SQL
This is an abstract library used by relations-mysql, relations-postgresql, relations-sqlite, etc. to generate queries specific to those databases.
But folks may find it useful for their usages. So here's some of the main unittests so you get the general idea.
I'll probably switch this to ANSI SQL? I dunno. Not sure how useful that'll be.
import
All the classes are capitalized to prevent collisions with reserved keywords. Plus it looks like actual SQL. So you can do a full import without worries.
from relations_sql import *select
query = SELECT("*").OPTIONS("FAST").FROM("people").WHERE(stuff__gt="things")
query.generate()
self.assertEqual(query.sql, "SELECT FAST * FROM `people` WHERE `stuff`>%s")
self.assertEqual(query.args, ["things"])
query = SELECT(
"*"
).FROM(
people=SELECT(
"a.b.c"
).FROM(
"d.e"
)
).WHERE(
stuff__in=SELECT(
"f"
).FROM(
"g"
).WHERE(
things__a__0___1____2_____3__gt=5
)
)
query.generate()
self.assertEqual(query.sql,
"SELECT * FROM (SELECT `a`.`b`.`c` FROM `d`.`e`) "
"AS `people` WHERE `stuff` IN "
"(SELECT `f` FROM `g` WHERE `things`#>>%s>JSON(%s))"
)
self.assertEqual(query.args, ['$."a"[0][-1]."2"."-3"', '5'])
query.GROUP_BY("fee", "fie").HAVING(foe="fum").ORDER_BY("yin", yang=DESC).LIMIT(1, 2)
query.generate()
self.assertEqual(query.sql,
"SELECT * FROM (SELECT `a`.`b`.`c` FROM `d`.`e`) "
"AS `people` WHERE `stuff` IN "
"(SELECT `f` FROM `g` WHERE `things`#>>%s>JSON(%s)) "
"GROUP BY `fee`,`fie` HAVING `foe`=%s "
"ORDER BY `yin`,`yang` DESC LIMIT %s,%s"
)
self.assertEqual(query.args, ['$."a"[0][-1]."2"."-3"', '5', 'fum', 1, 2])insert
query = INSERT("people").VALUES(stuff=1, things=2).VALUES(3, 4)
query.generate()
self.assertEqual(query.sql,"INSERT INTO `people` (`stuff`,`things`) VALUES (%s,%s),(%s,%s)")
self.assertEqual(query.args, [1, 2, 3, 4])
query = INSERT("people").OPTIONS("FAST")
query.SELECT("stuff").FROM("things")
query.generate()
self.assertEqual(query.sql,"INSERT FAST INTO `people` SELECT `stuff` FROM `things`")
self.assertEqual(query.args, [])
query = INSERT("people").VALUES(stuff=1, things=2).VALUES(3, 4)
query.SELECT("stuff").FROM("things")
self.assertRaisesRegex(relations_sql.SQLError, "set VALUES or SELECT but not both", query.generate)update
query = UPDATE("people").SET(stuff="things").WHERE(things="stuff")
query.OPTIONS("FAST").ORDER_BY("yin", yang=DESC).LIMIT(5)
query.generate()
self.assertEqual(query.sql, "UPDATE FAST `people` SET `stuff`=%s WHERE `things`=%s ORDER BY `yin`,`yang` DESC LIMIT %s")
self.assertEqual(query.args, ["things", "stuff", 5])
query.LIMIT(10)
self.assertRaisesRegex(relations_sql.SQLError, "LIMIT can only be total", query.generate)delete
query = DELETE("people").WHERE(things="stuff")
query.OPTIONS("FAST").ORDER_BY("yin", yang=DESC).LIMIT(5)
query.generate()
self.assertEqual(query.sql, "DELETE FAST FROM `people` WHERE `things`=%s ORDER BY `yin`,`yang` DESC LIMIT %s")
self.assertEqual(query.args, ["stuff", 5])
query.LIMIT(10)
self.assertRaisesRegex(relations_sql.SQLError, "LIMIT can only be total", query.generate)inherit
The unittest demonstrate how to abstract through inheritance. Classes know which other classes they'll need through class attributes.
The unittests are where the backticks escaping comes from. It's not a default or anything.
In test_clause.py
class OPTIONS(relations_sql.OPTIONS):
pass
class FIELDS(relations_sql.FIELDS):
ARGS = test_expression.FIELD
KWARG = test_expression.FIELD
KWARGS = test_expression.ASIn test_query.py
class SELECT(relations_sql.SELECT):
CLAUSES = collections.OrderedDict([
("OPTIONS", test_clause.OPTIONS),
("FIELDS", test_clause.FIELDS),
("FROM", test_clause.FROM),
("WHERE", test_clause.WHERE),
("GROUP_BY", test_clause.GROUP_BY),
("HAVING", test_clause.HAVING),
("ORDER_BY", test_clause.ORDER_BY),
("LIMIT", test_clause.LIMIT)
])
class INSERT(relations_sql.INSERT):
CLAUSES = collections.OrderedDict([
("OPTIONS", test_clause.OPTIONS),
("TABLE", test_expression.TABLE),
("FIELDS", test_expression.NAMES),
("VALUES", test_clause.VALUES),
("SELECT", SELECT)
])
class UPDATE(relations_sql.UPDATE):
CLAUSES = collections.OrderedDict([
("OPTIONS", test_clause.OPTIONS),
("TABLE", test_expression.TABLE),
("SET", test_clause.SET),
("WHERE", test_clause.WHERE),
("ORDER_BY", test_clause.ORDER_BY),
("LIMIT", test_clause.LIMIT)
])
class DELETE(relations_sql.DELETE):
CLAUSES = collections.OrderedDict([
("OPTIONS", test_clause.OPTIONS),
("TABLE", test_expression.TABLE),
("WHERE", test_clause.WHERE),
("ORDER_BY", test_clause.ORDER_BY),
("LIMIT", test_clause.LIMIT)
])In the case of query classes, the OrderedDict's there also specific the order in which clauses are generated and appended. So it's pretty easy to extend.
