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.

Finch
  • 71
  • 1
  • 9
  • You can use the subprocess module to interact with sqlplus or MySQL. – Zenith_1024 May 27 '22 at 07: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; --`. – Panagiotis Kanavos May 27 '22 at 07:40
  • @Raytheon_11 `subprocess` has nothing to do with database access or SQL injections, this query's problem – Panagiotis Kanavos May 27 '22 at 07:41
  • You can refer here to run a .sql file --> https://www.bobbydurrettdba.com/2016/11/04/running-sqlplus-from-a-python-script/ – Zenith_1024 May 27 '22 at 07:41
  • @PanagiotisKanavos Misunderstood the question, thought OP wanted to run a .sql file with Python – Zenith_1024 May 27 '22 at 07:42
  • @Raytheon_11 even then, why start another process to execute SQL commands instead of just executing those SQL commands? – Panagiotis Kanavos May 27 '22 at 07:43
  • @PanagiotisKanavos Could you clarify what you mean by 'just executing those SQL commands' – Zenith_1024 May 27 '22 at 07:44
  • @Maddy9 what are you trying to do? Even without SQL injection, executing 100 SELECTs is over 100 times slower than a single SELECT that loads all results. `SELECT ... Where ID IN (1,5,23,456)` will return all results whose IDs are in the list. The accepted answer in [this question](https://stackoverflow.com/questions/2253494/passing-param-to-db-execute-for-where-in-int-list) constructs a *parameterized* query with an IN clause and then executes the query with parameters with `c.execute(stmt,prms)`. – Panagiotis Kanavos May 27 '22 at 07:50

0 Answers0