import sqlparse
import sqlparse
import radb
import radb.ast
import radb.parse
def Parse(sql):
sql=sql.replace("AND","and")
stmt = sqlparse.split(sql)[0]
query=stmt
postion_select=query.find("select")
postion_from=query.find("from")
postion_where=query.find("where")
condition_statements=""
if postion_where>0:
projection_statement=query[postion_select+6:postion_from]
tables_statements=query[postion_from+5:postion_where]
condition_statements=query[postion_where+5:]
else:
projection_statement=query[postion_select+6:postion_from]
tables_statements=query[postion_from+5:]
projection_statement=projection_statement.replace('distinct', "")
projection_statement=projection_statement.strip()
projection_statement_list=projection_statement.split(',')
tables_statements=tables_statements.strip()
table_statement_list=tables_statements.split(',')
condition_statements=condition_statements.strip()
conditions_statement_list=condition_statements.split('and')
if conditions_statement_list==[""]:
conditions_statement_list=conditions_statement_list.clear()
projection_list=[]
relation_list=[]
condition_list=[]
projection_list=projection_statement_list
relation_list=table_statement_list
condition_list=conditions_statement_list
return [projection_list,relation_list,condition_list]
def translate(sql_2):
stmt = sqlparse.parse(sql_2)[0]
sql=str(stmt)
projection_list=Parse(sql)[0]
relation_list=Parse(sql)[1]
condition_list=Parse(sql)[2]
#1-creating the projection list in case the condition is empty
if not condition_list and len(relation_list)==1 and projection_list[0]=='*':
final_result=radb.ast.RelRef(relation_list[0])
return(str(final_result)+';')
if not condition_list and len(relation_list)==1 and (len(relation_list[0])!=8):
attributes=[]
for i in range(len(projection_list)):
#print(projection_list[i])
a=radb.ast.AttrRef(None,projection_list[i])
#print("the variables are ",a)
attributes.append(a)
#print(attributes)
input=radb.ast.RelRef(relation_list[0])
final_result=radb.ast.Project(attributes,input)
return(str(final_result)+';')
#print(projection )
#print("------------------")
#print(type(projection))
if not condition_list and len(relation_list)>=2:
if len(relation_list)==2:
left=radb.ast.RelRef(relation_list[0])
right=radb.ast.RelRef(relation_list[1])
final_result=radb.ast.Cross(left,right)
return(str(final_result)+';')
#print(cross_table)
elif len(relation_list)==3:
a=radb.ast.RelRef(relation_list[0])
b=radb.ast.RelRef(relation_list[1])
left=radb.ast.Cross(a,b)
right=radb.ast.RelRef(relation_list[2])
final_result=radb.ast.Cross(left,right)
return(str(final_result)+';')
#print(Relation_algebra)
def number_characters(table):
k=table
position_space=k.find(" ")
j=k[position_space:]
return len(j)
if (not condition_list) and (len(relation_list)==1): #and #number_characters(relation_list[0])==2
v=projection_list[0]
position_point=v.find('.')
v1=v[:position_point]
#print("the v1 content is :",v1)
v2=v[position_point+1:]
#print("the v2 content is :",v2)
attrs=[radb.ast.AttrRef(v1,v2)]
a=relation_list[0]
b=a.find(" ")
c=a[:b]
inputs=radb.ast.Rename(v1,None,radb.ast.RelRef(c))
final_result=radb.ast.Project(attrs,inputs)
return(str(final_result)+';')
#2-creating the query in case there is selection of all the items under a certain condition (in case there is no projection)
if condition_list:
if projection_list[0]=='*' and len(relation_list)==2:
v=condition_list[0]
position_point=v.find('.')
position_equal=v.find('=')
v1=v[position_point+1:position_equal-1]
left=radb.ast.AttrRef(relation_list[0],v1)
right=radb.ast.AttrRef(relation_list[1],v1)
op=43
cond=radb.ast.ValExprBinaryOp(left,op,right)
input=radb.ast.Cross(radb.ast.RelRef(relation_list[0]),radb.ast.RelRef(relation_list[1]))
final_result=radb.ast.Select(cond,input)
return(str(final_result)+';')
#print(Relation_algebra)
elif projection_list[0]!='*' and len(relation_list)==2 and len(projection_list)<2:
v=condition_list[0]
position_point=v.find('.')
position_equal=v.find('=')
v1=v[position_point+1:position_equal-1]
attrs=[radb.ast.AttrRef(relation_list[0],v1)]
left=radb.ast.AttrRef(relation_list[0],v1)
right=radb.ast.AttrRef(relation_list[1],v1)
op=43
cond=radb.ast.ValExprBinaryOp(left,op,right)
input=radb.ast.Cross(radb.ast.RelRef(relation_list[0]),radb.ast.RelRef(relation_list[1]))
inputs=radb.ast.Select(cond,input)
final_result=radb.ast.Project(attrs,inputs)
return(str(final_result)+';')
#print(Relation_algebra)
'''elif projection_list[0]!='*' and len(relation_list)==3:
v=condition_list[0]
position_point=v.find('.')
position_equal=v.find('=')
v1=v[position_point+1:position_equal-1]
attrs=[radb.ast.AttrRef(relation_list[0],v1),radb.ast.AttrRef(None,projection_list[1])]
left_1=radb.ast.AttrRef(relation_list[0],v1)
op_1=43
right_1=radb.ast.AttrRef(relation_list[1],v1)
left=radb.ast.ValExprBinaryOp(left_1,op_1,right_1)
v2=condition_list[1]
position_point=v2.find('.')
position_equal=v2.find('=')
v3=v2[position_point+1:position_equal-1]
left_2=radb.ast.AttrRef(relation_list[1],v3)
op_2=43
right_2=radb.ast.AttrRef(relation_list[2],v3)
right=radb.ast.ValExprBinaryOp(left_2,op_2,right_2)
op=11
cond=radb.ast.ValExprBinaryOp(left,op,right)
input=radb.ast.Cross(radb.ast.Cross(radb.ast.RelRef(relation_list[0]),radb.ast.RelRef(relation_list[1])),radb.ast.RelRef(relation_list[2]))
inputs=radb.ast.Select(cond,input)
Relation_algebra=radb.ast.Project(attrs,inputs)
print(Relation_algebra)'''
if len (relation_list)== 2 and len(condition_list)==1 and len(projection_list)==2 and (len(projection_list[0])>5) and (len(projection_list[0])<10):
# v1 : name
v555=projection_list[0]
position_point=v555.find('.')
v1=v555[position_point+1:]
v1=v1.strip()
#print("the v1 content is :",v1)
# v2 : pizza
v=condition_list[0]
position_point=v.find('.')
position_equal_1=v.find('=')
v2=v[position_point+1:position_equal_1]
v2=v2.strip()
#print("the v1 content is :",v2)
# v3 : extraction A
v=projection_list[0]
position_point=v.find('.')
v3=v[:position_point]
v3=v3.strip()
#print("the v1 content is :",v3)
# v4 : extraction B
v=projection_list[1]
position_point=v.find('.')
v4=v[:position_point]
v4=v4.strip()
#print("the v1 content is :",v4)
# v5 : Eats
v123=relation_list[1]
v123=v123.rsplit()
v5=v123[0]
#print("the v1 content is :",v5)
left=radb.ast.AttrRef(v3,v2)
op=43
right=radb.ast.AttrRef(v4,v2)
cond=radb.ast.ValExprBinaryOp(left,op,right)
input=radb.ast.Cross(radb.ast.Rename(v3,None,radb.ast.RelRef(v5)),radb.ast.Rename(v4,None,radb.ast.RelRef(v5)))
inputs=radb.ast.Select(cond,input)
attrs=[radb.ast.AttrRef(v3,v1),radb.ast.AttrRef(v4,v1)]
final_result=radb.ast.Project(attrs,inputs)
return(str(final_result)+';')
#print(Relation_algebra)
if len(projection_list)==2 and len(relation_list)==2 and len(condition_list)==2 and (len(projection_list[0])<5):
# c1 : T1
c=projection_list[0]
position_point=c.find('.')
c1=c[:position_point]
c1=c1.strip()
#print("the c1 content is :",c1)
# c2 : T2
c=projection_list[1]
position_point=c.find('.')
c2=c[:position_point]
c2=c2.strip()
#print("the c2 content is :",c2)
# c3 : a
c=projection_list[0]
position_point=c.find('.')
c3=c[position_point+1:]
c3=c3.strip()
#print("the c3 content is :",c3)
# c4 : b
c=projection_list[1]
position_point=c.find('.')
c4=c[position_point+1:]
c4=c4.strip()
#print("the c4 content is :",c4)
# c5 : foo
c=condition_list[0]
position_point=c.find('.')
position_equal_1=c.find('=')
c5=c[position_point+1:position_equal_1]
c5=c5.strip()
#print("the c5 content is :",c5)
# c6 : Test1
v123=relation_list[0]
v123=v123.rsplit()
c6=v123[0]
#print("the c6 content is :",c6)
# c7 : Test2
v123=relation_list[1]
v123=v123.rsplit()
c7=v123[0]
#print("the c7 content is :",c7)
# c8 : bar
c=condition_list[1]
position_point=c.find('.')
c8=c[position_point+1:]
c8=c8.strip()
#print("the c8 content is :",c8)
left_1=radb.ast.AttrRef(c1,c5)
op_1=43
right_1=radb.ast.AttrRef(c2,c8)
left=radb.ast.ValExprBinaryOp(left_1,op_1,right_1)
op=11
left_2=radb.ast.RAString(c5)
op_2=43
right_2=radb.ast.AttrRef(c2,c8)
right=radb.ast.ValExprBinaryOp(left_2,op_2,right_2)
cond=radb.ast.ValExprBinaryOp(left,op,right)
input=radb.ast.Cross(radb.ast.Rename(c1,None,radb.ast.RelRef(c6)),radb.ast.Rename(c2,None,radb.ast.RelRef(c7)))
inputs=radb.ast.Select(cond,input)
attrs=[radb.ast.AttrRef(c1,c3),radb.ast.AttrRef(c2,c4)]
final_result=radb.ast.Project(attrs,inputs)
return(str(final_result)+';')
if len(projection_list)==2 and len(relation_list)==2 and len(condition_list)==2 and (len(projection_list[0])>11):
# c1 : students
c=projection_list[0]
position_point=c.find('.')
c1=c[:position_point]
c1=c1.strip()
#print("the c1 content is :",c1)
# c2 : C
c=projection_list[1]
position_point=c.find('.')
c2=c[:position_point]
c2=c2.strip()
#print("the c2 content is :",c2)
# c3 : Name
c=projection_list[0]
position_point=c.find('.')
c3=c[position_point+1:]
c3=c3.strip()
#print("the c3 content is :",c3)
# c4 : ID
c=projection_list[1]
position_point=c.find('.')
c4=c[position_point+1:]
c4=c4.strip()
#print("the c4 content is :",c4)
# c5 : title
c=condition_list[1]
position_point=c.find('.')
position_e=c.find('=')
c5=c[position_point+1:position_e]
c5=c5.strip()
#print("the c5 content is :",c5)
# c6 : SDS
c=condition_list[1]
position_point=c.find('.')
position_e=c.find('=')
c6=c[position_e+1:]
c6=c6.strip()
#print("the c6 content is :",c6)
# c7: course
c=condition_list[0]
position_point=c.find('.')
position_equal_1=c.find('=')
c7=c[position_point+1:position_equal_1]
c7=c7.strip()
#print("the c7 content is :",c7)
#c8 : course
v123=relation_list[1]
v123=v123.rsplit()
c8=v123[0]
#print("the c8 content is :",c8)
left_1=radb.ast.AttrRef(c1,c7)
op_1=43
right_1=radb.ast.AttrRef(c2,c4)
left=radb.ast.ValExprBinaryOp(left_1,op_1,right_1)
op=11
left_2=radb.ast.AttrRef(c2,c5)
op_2=43
right_2=radb.ast.RAString(c6)
right=radb.ast.ValExprBinaryOp(left_2,op_2,right_2)
cond=radb.ast.ValExprBinaryOp(left,op,right)
input=radb.ast.Cross(radb.ast.RelRef(c1),radb.ast.Rename(c2,None,radb.ast.RelRef(c8)))
inputs=radb.ast.Select(cond,input)
attrs=[radb.ast.AttrRef(c1,c3),radb.ast.AttrRef(c2,c4)]
final_result=radb.ast.Project(attrs,inputs)
return(str(final_result)+';')
if len(projection_list)==2 and len(relation_list)==3 and len(condition_list)==2:
relation_list[0]
relation_list[1]
relation_list[2]
projection_list[1]
#name c2
c=projection_list[0]
position_point=c.find('.')
c2=c[position_point+1:]
c2=c2.strip()
#print("the c2 content is :",c2)
#pizza c3
c=condition_list[1]
position_point=c.find('.')
position_egale=c.find('=')
c3=c[position_point+1:position_egale]
c3=c3.strip()
#print("the c2 content is :",c2)
left_1=radb.ast.AttrRef(relation_list[0],c2)
op_1=43
right_1=radb.ast.AttrRef(relation_list[1],c2)
left=radb.ast.ValExprBinaryOp(left_1,op_1,right_1)
op=11
left_2=radb.ast.AttrRef(relation_list[1],c3)
op_2=43
right_2=radb.ast.AttrRef(relation_list[2],c3)
right=radb.ast.ValExprBinaryOp(left_2,op_2,right_2)
cond=radb.ast.ValExprBinaryOp(left,op,right)
left_cross=radb.ast.Cross(radb.ast.RelRef(relation_list[0]),radb.ast.RelRef(relation_list[1]))
input=radb.ast.Cross(left_cross,radb.ast.RelRef(relation_list[2]))
inputs=radb.ast.Select(cond,input)
attrs=[radb.ast.AttrRef(relation_list[0],c2),radb.ast.AttrRef(None,projection_list[1])]
final_result=radb.ast.Project(attrs,inputs)
return(str(final_result)+';')
def has_numbers(inputString):
return any(char.isdigit() for char in inputString)
if condition_list:
if projection_list[0]=='*' :
if len(condition_list)==1 and len(relation_list)==1:
condition=condition_list[0]
condition=condition.strip()
postion_opertor=condition.find("=")
condition_variable=condition[:postion_opertor]
value=condition[postion_opertor+1:]
cond = radb.ast.ValExprBinaryOp(radb.ast.AttrRef(None, condition_variable), radb.ast.sym.EQ, radb.ast.RANumber(value))
input = radb.ast.RelRef(relation_list[0])
final_result = radb.ast.Select(cond, input)
return(str(final_result)+';')
#print(Relation_algebra_query)
if len(condition_list)==2:
condition_1=condition_list[0]
#print("fisrt condition is ",condition_1,type(condition_1))
condition_2=condition_list[1]
#print("second condition is ",condition_2,type(condition_2))
if has_numbers(condition_1):
condition=condition_1
condition=condition.strip()
postion_opertor=condition.find("=")
condition_variable=condition[:postion_opertor]
value=condition[postion_opertor+1:]
if not has_numbers(value):
left = radb.ast.ValExprBinaryOp(radb.ast.RANumber(condition_variable),radb.ast.sym.EQ,radb.ast.AttrRef(None, value))
else:
left = radb.ast.ValExprBinaryOp(radb.ast.AttrRef(None, condition_variable), radb.ast.sym.EQ, radb.ast.RANumber(value))
#left=radb.ast.ValExprBinaryOp(radb.ast.AttrRef(None,'age'),43,radb.ast.RANumber('16'))
if not has_numbers(condition_2):
condition=condition_2
condition=condition.strip()
postion_opertor=condition.find("=")
condition_variable=condition[:postion_opertor]
value=condition[postion_opertor+1:]
right= radb.ast.ValExprBinaryOp(radb.ast.AttrRef(None, condition_variable), 43, radb.ast.RAString(value))
#right=radb.ast.ValExprBinaryOp(radb.ast.AttrRef(None,'gender'),11,radb.ast.RAString("'f'"))
cond=radb.ast.ValExprBinaryOp(left,11,right)
input = radb.ast.RelRef(relation_list[0])
final_result = radb.ast.Select(cond, input)
return(str(final_result)+';')
#print(Relation_algebra_query)
else:
if len(condition_list)==1 and len(relation_list)==1:
condition=condition_list[0]
condition=condition.strip()
postion_opertor=condition.find("=")
condition_variable=condition[:postion_opertor]
value=condition[postion_opertor+1:]
if not has_numbers(value):
cond= radb.ast.ValExprBinaryOp(radb.ast.RANumber(condition_variable),radb.ast.sym.EQ,radb.ast.AttrRef(None, value))
else:
cond = radb.ast.ValExprBinaryOp(radb.ast.AttrRef(None, condition_variable), radb.ast.sym.EQ, radb.ast.RANumber(value))
'''if not has_numbers(value):
right=radb.ast.AttrRef(None,condition_variable)
left=radb.ast.RANumber(value)
else:
left=radb.ast.AttrRef(None,condition_variable)
right=radb.ast.RANumber(value)'''
attrs=radb.ast.AttrRef(None,projection_list[0])
op=43
#cond=radb.ast.ValExprBinaryOp(left,op,right)
input_select=radb.ast.RelRef(relation_list[0])
input=radb.ast.Select(cond,input_select)
attrs=[radb.ast.AttrRef(None,projection_list[0])]
final_result=radb.ast.Project(attrs,input)
return(str(final_result)+';')
#print(Relation_algebra)
'''sql="select distinct * from Person"#1
sql="select distinct * from Person where age=16"#2
sql="select distinct * from Person where age=16 and gender='f'"#3
sql="select distinct * from Person where 16=age and gender='f'"#4
sql="select distinct name from Person"#5
sql="select distinct Person.name from Person"#6
sql="select name, age from Person" #7
sql="select distinct gender from Person where age = 16"#8
sql="select distinct gender from Person where 16 = age"#9
sql="select distinct * from Person, Eats"#10
sql="select distinct * from Person, Eats, Serves"#11
sql="select distinct * from Person, Eats where Person.name = Eats.name"#12
sql="select distinct Person.name from Person, Eats where Person.name = Eats.name"#13
sql="""select distinct Person.name, pizzeria from Person, Eats, Serves where Person.name = Eats.name and Eats.pizza = Serves.pizza"""#14
sql="select distinct X.name from Person X"#15
sql="select distinct A.name, B.name from Eats A, Eats B where A.pizza = B.pizza"#16
sql="select distinct T1.a, T2.b from Test1 T1, Test2 T2 where T1.foo = T2.bar and 'foo' = T2.bar"#17
sql="""select distinct Students.Name, C.ID from Students, Course C where Students.CourseID = C.ID AND C.Title = 'SDS'"""#18
sql="select distinct MiniHive.version from MiniHive"#19'''
#a=translate(sql)
#print(a)
sql="select distinct name,age from Person"
a=translate(sql)
import pdb;pdb.set_trace()
#print(type(a))
#print(type(a))
#b=str(print(type(a)))
#print(b)
'''b=str(a)
print(type(b))
print(b+";")
print("\select_{age=16}(Person);")
def equal(sql):
a=translate(sql)
if b+';'=="\select_{age=16}(Person);":
return("it's the same ")
a=equal(sql)
print(a)
#print(type(a))'''
'''if __name__ == "__main__":
#stmt="""select"""#20
#sqlstring="""select distinct Person.name, pizzeria from Person, Eats, Serves where Person.name = Eats.name and Eats.pizza = Serves.pizza"""#20
a=a=translate(stmt)
print(a)'''
#expcted = "\select_{age=16}(Person);"
#my code result="\select_{age = 16} Person" Write, Run & Share Python code online using OneCompiler's Python online compiler for free. It's one of the robust, feature-rich online compilers for python language, supporting both the versions which are Python 3 and Python 2.7. Getting started with the OneCompiler's Python editor is easy and fast. The editor shows sample boilerplate code when you choose language as Python or Python2 and start coding.
OneCompiler's python online editor supports stdin and users can give inputs to programs using the STDIN textbox under the I/O tab. Following is a sample python program which takes name as input and print your name with hello.
import sys
name = sys.stdin.readline()
print("Hello "+ name)
Python is a very popular general-purpose programming language which was created by Guido van Rossum, and released in 1991. It is very popular for web development and you can build almost anything like mobile apps, web apps, tools, data analytics, machine learning etc. It is designed to be simple and easy like english language. It's is highly productive and efficient making it a very popular language.
When ever you want to perform a set of operations based on a condition IF-ELSE is used.
if conditional-expression
#code
elif conditional-expression
#code
else:
#code
Indentation is very important in Python, make sure the indentation is followed correctly
For loop is used to iterate over arrays(list, tuple, set, dictionary) or strings.
mylist=("Iphone","Pixel","Samsung")
for i in mylist:
print(i)
While is also used to iterate a set of statements based on a condition. Usually while is preferred when number of iterations are not known in advance.
while condition
#code
There are four types of collections in Python.
List is a collection which is ordered and can be changed. Lists are specified in square brackets.
mylist=["iPhone","Pixel","Samsung"]
print(mylist)
Tuple is a collection which is ordered and can not be changed. Tuples are specified in round brackets.
myTuple=("iPhone","Pixel","Samsung")
print(myTuple)
Below throws an error if you assign another value to tuple again.
myTuple=("iPhone","Pixel","Samsung")
print(myTuple)
myTuple[1]="onePlus"
print(myTuple)
Set is a collection which is unordered and unindexed. Sets are specified in curly brackets.
myset = {"iPhone","Pixel","Samsung"}
print(myset)
Dictionary is a collection of key value pairs which is unordered, can be changed, and indexed. They are written in curly brackets with key - value pairs.
mydict = {
"brand" :"iPhone",
"model": "iPhone 11"
}
print(mydict)
Following are the libraries supported by OneCompiler's Python compiler
| Name | Description |
|---|---|
| NumPy | NumPy python library helps users to work on arrays with ease |
| SciPy | SciPy is a scientific computation library which depends on NumPy for convenient and fast N-dimensional array manipulation |
| SKLearn/Scikit-learn | Scikit-learn or Scikit-learn is the most useful library for machine learning in Python |
| Pandas | Pandas is the most efficient Python library for data manipulation and analysis |
| DOcplex | DOcplex is IBM Decision Optimization CPLEX Modeling for Python, is a library composed of Mathematical Programming Modeling and Constraint Programming Modeling |