universal-sql-builder

Universal SQL Builder

SQL framework ORM independent, it helps you build sql query in python
What ever you using Hive, HBase, Spark* SQL, Django & FLASK etc so you can gernate simple SQL query
Its return raw sql query string this can be use in any Framework

Installation

Step 1: pip install universal-sql-builder
Step 2: from UniversalSqlBuilder import UniversalSqlBuilder
Step 3: Flow the examples....

Select

UniversalSqlBuilder.table("employee").select("id,name").select(",department").get()

Where

You can use key and value based parameters : where("city=", "ABC")
Or you can use raw where query : where("col=(select id from users)")
UniversalSqlBuilder.table("employee").select("id,name").select(",department").where("ram=(select id from users)").where("city=", "delhi").get()

OrWhere and WhereBetween

If you want data according min and max use :
whereBetween("age",[200,300])  UniversalSqlBuilder.table("employee").select("id,name").whereBetween("age",[200,300]).get()
Using OR with where :
UniversalSqlBuilder.table("employee").select("id,name").orWhere("seller=",'100').get()

GroupBy, OrderBy amd Limit

Use of group by : groupBy("name")

UniversalSqlBuilder.table("employee").select("id,name").groupBy("name").get()

Use order by : orderBy("ID","desc") 

UniversalSqlBuilder.table("employee").select("id,name").orderBy("ID","desc").get()

use with limit : limit(10,20)

UniversalSqlBuilder.table("employee").select("id,name").limit(10,20).get()

Join ('FULL OUTER', 'INNER', 'LEFT', 'RIGHT', 'JOIN')

Default : join("users","users.id=employee.emp_id")

FULL OUTER : join("users","users.id=employee.emp_id","FULL OUTER")

INNER : join("users","users.id=employee.emp_id","INNER")

LEFT : join("users","users.id=employee.emp_id","LEFT")

RIGHT : join("users","users.id=employee.emp_id","RIGHT")

JOIN : join("users","users.id=employee.emp_id","JOIN")

Having & OrHaving

Using OR with Having :
UniversalSqlBuilder.table("employee").select("id,name").orHaving("seller=",'100').get()

You can use key and value based parameters : having("city=", "ABC")
Or you can use raw where query : having("col=(select id from users)")

UniversalSqlBuilder.table("employee").select("id,name").select(",department").having("id=(select id from users)").having("city=", "delhi").get()

Making Advance Query

Using OR with Having :
print(UniversalSqlBuilder.table("employee") .select("id,name") .select(",department") .where("city=", "delhi") .where("order_id=("+UniversalSqlBuilder.table("orders as temp").select("temp.id").orderBy("temp.id", "desc").get()+")") .orWhere("seller=", '100') .orWhere("brand=", 'rock') .having("city=", "delhi") .orHaving("seller=", '100') .orHaving("brand=", 'rock') .whereBetween("age", [200, 300]) .join("users", "users.id=employee.emp_id", "left") .join("cars", "cars.id=employee.emp_id") .groupBy("name") .orderBy("ID", "desc") .limit(10, 20).get())

SELECT id, name, department FROM employee LEFT JOIN users ON users.id = employee.emp_id INNER JOIN cars ON cars.id = employee.emp_id WHERE city = 'delhi' AND order_id = (SELECT temp.id FROM orders AS temp ORDER BY temp.id DESC) AND age >= 200 AND age <= 300 AND (seller = '100' OR brand = 'rock') GROUP BY name HAVING city = 'delhi' AND (seller = '100' OR brand = 'rock') ORDER BY ID DESC LIMIT 20 , 10