python-relations-mysql
Module for interacting with Relations and MySQL. It generates MySQL compatible SQL from SQL like classes in Python.
This is the SQL library used by relations-pymysql, 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_mysql 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(
"*"
).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 `things`->>%s>%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 FAST * FROM (SELECT `a`.`b`.`c` FROM `d`.`e`) "
"AS `people` WHERE `stuff` IN "
"(SELECT `f` FROM `g` WHERE `things`->>%s>%s) "
"GROUP BY `fee`,`fie` HAVING `foe`=%s "
"ORDER BY `yin`,`yang` DESC LIMIT %s OFFSET %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)
