0

I have a dict which give Key (k1) and Value as (v1). And what I am looking for is to append v1 to queries stored in .sql or .csv file

def getDetailsFromRDS():
    t = collect_CabItem() # stores Key and Value. Ex. {'http://checkCab:8080/city/ab' : [],'http://checkCab:8080/city/cd' : ['1239','5832', '4422'],'http://checkCab:8080/city/ef' : ['5832', '4422'],'http://checkCab:8080/city/gh' : ['Geo','Part'],'http://checkCab:8080/city/ij' : ['Kelly', '123-450'], } 
   # In dict http://* part is key and whatever in [] are individual values which I need to append to queries in where clause.
    cabinfo =[] # empty list and later used by separate function for creating Ref_Cab.csv file

    for k1, Cabid1 in t.items():
        for v1 in Cabid1: 
            print("Key and associated value:",k1,v1)
            with open ('Test.sql','r') as Q_csv_file:
                file = csv.reader(Q_csv_file)

                for row in file:


                    query_1_table = (str(row), '%s' % (x)) # this is what I am trying but gives an error as queries from file treated as tuple 
                    #query_1_table = (str(row)+ '%s' % (x)) # this is what I tried but gives an error as queries from file treated as list
                    #query_1_table = ("""SELECT * FROM cab.pasDetails where cab_id =  '%s'""" % (v1)) # this was earlier code which was working fine
                    #query_2_table = ("""SELECT * FROM cab.locDetails where cab_p_id =  '%s'""" % (v1)) # this was earlier code which was working fine

                    print(query_1_table )
                    mycur.execute(query_1_table)
                    rds_details_query_1_table = mycur.fetchall()
                    print(rds_details_query_1_table )

                    #print(#query_2_table) # this was earlier code
                    #mycur.execute(#query_2_table) # this was earlier code
                    #rds_details_query_2_table = mycur.fetchall() # this was earlier code
                    #print(rds_details_query_2_table ) # this was earlier code

            cabinfo.append(((k1, v1,rds_details_query_1_table,rds_details_query_2_table ))) 

                return cabinfo

Now I need to store all queries (around 15-20) in the file and then append all values from dict to the queries one by one. Result will be stored as list and this list can be consumed by the function for new file creation Can anyone guide me, how can I read and execute all sql statement either from .sql file or from .csv file and then read and append attrib stored as dict value in a loop? I know there might be an issue of sql injection as I am trying to append external data to query. Any suggestion for that welcome as well Many thanks. Cheers.

8
  • You can use the subprocess module to interact with sqlplus or MySQL. Commented May 27, 2022 at 7:40
  • Use parameters instead of appending. What you do is how SQL injection attacks happen. Imagine what would happen if one of those files contained '); DROP TABLE Users; --. Commented May 27, 2022 at 7:40
  • @Raytheon_11 subprocess has nothing to do with database access or SQL injections, this query's problem Commented May 27, 2022 at 7:41
  • You can refer here to run a .sql file --> bobbydurrettdba.com/2016/11/04/… Commented May 27, 2022 at 7:41
  • @PanagiotisKanavos Misunderstood the question, thought OP wanted to run a .sql file with Python Commented May 27, 2022 at 7:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.