python-relations-sqlite
Module for interacting with Relations and SQLite. It generates SQLite compatible SQL from SQL like classes in Python.
This is the SQL library used by relations-sqlite3, but folks may find it useful. So here's some of the main unittests so you get the general idea.
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_sqlite import *select
query = SELECT("*").OPTIONS("FAST").FROM("people").WHERE(stuff__gt="things")
query.generate()
self.assertEqual(query.sql, """SELECT FAST * FROM `people` WHERE `stuff`>?""")
self.assertEqual(query.args, ["things"])
query = SELECT(
"*"
).OPTIONS(
"FAST"
).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 FAST * FROM (SELECT `a`.`b`.`c` FROM `d`.`e`) "
"AS `people` WHERE `stuff` IN "
"(SELECT `f` FROM `g` WHERE json_extract(`things`,?)>?)"
)
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 FAST * FROM (SELECT `a`.`b`.`c` FROM `d`.`e`) "
"AS `people` WHERE `stuff` IN "
"(SELECT `f` FROM `g` WHERE json_extract(`things`,?)>?) "
"GROUP BY `fee`,`fie` HAVING `foe`=? "
"ORDER BY `yin`,`yang` DESC LIMIT ? OFFSET ?"
)
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 (?,?),(?,?)")
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`=? WHERE `things`=? ORDER BY `yin`,`yang` DESC LIMIT ?""")
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`=? ORDER BY `yin`,`yang` DESC LIMIT ?""")
self.assertEqual(query.args, ["stuff", 5])
query.LIMIT(10)
self.assertRaisesRegex(relations_sql.SQLError, "LIMIT can only be total", query.generate)
