parameters substitution from one .py file to another .py file

Question

I have a parameter file with all param values . I want to pass these values to another .py (raw sql file) and generate an executable sql file Here is what I have so far

params.py file

var1 = '1,2,3'
var2 = '('abc', 'cde', 'xyz')'

rawsql.py

import params

select *
from emp.ids
where ids in var1
and names in var2

The output I'm expecting is a .sql file

exec_sql_file.sql

select *
from emp.ids
where ids in (1,2,3) and 
and names in ('abc','code','xyz')

How can I accomplish the variable substation in a .py file from another .py file

Doing this with simple string replacements will leave you open to accidental or malicious **SQL Injection**. Do it instead with a library, such as SQLAlchemy, or take a look at this http://stackoverflow.com/questions/1633332/how-to-put-parameterized-sql-query-into-variable-and-then-execute-in-python — salezica, Jun 14 '16 at 20:48
Seems like it might be better to use a good templating solution, something like jinja http://jinja.pocoo.org/ — user590028, Jun 14 '16 at 20:51
the sql string will be validated before execution. im lookinh for a simple varisble replacement solution. sql alchemy does not work with the databases i work with — pmv, Jun 14 '16 at 20:57

kardaj · Accepted Answer · 2016-06-16T12:25:39.653

0

The following snippet will give you and idea of what you need to do:

import params
# filter non built-in variable names
variables = [k for k in params.__dict__ if '__' not in k]

with open('rawsql.sql', 'r') as f:
    content = f.read()

for v in variables:
    content = content.replace(v, getattr(params, v))
with open('exec_sql_file.sql', 'w') as f:
    f.write(content)

Of course this is not bullet-proof and can be improved to fit your needs in terms of string formatting. I should also highlight that doing this will leave you open SQL injection, you should maybe go with a solution based on sqlalchemy or another lib specific to your server.

EDIT:

A python module is an object and every variable defined in such a module is an attribute. The information about a python object's attributes is stored in the object name space (its __dict__). By default, an empty module has the following attributes:

__builtins__
__file__
__name__
__doc__

These special attributes are left aside thanks to the condition (if '__' not in k)

edited Jun 16 '16 at 12:25

answered Jun 14 '16 at 20:58

kardaj

1,897
19
19

hi..i am trying to read data with certain parameters, so sql injection is not a concern here. in this case, does exec_sql_file gets generated from parms file and rawsql file? I want to see how can I generate the exec_sql_file from params file and rawsql file (parametrized file) – pmv Jun 15 '16 at 02:28
where is the params file located. i made chanes to param file in the same folder as ipython notebook. but these are not applied – pmv Jun 15 '16 at 23:01
in my setup, all files are in the same folder. Did you try executing the file directly from a terminal? Because a Notebook may be running it from another folder – kardaj Jun 16 '16 at 06:11
i wascable to fix the probkem. can you also add more comments on tbe code logic.. especially the first step on using dict object., thanks pmv – pmv Jun 16 '16 at 12:05
I added an *EDIT* section when I explained the logic. Hope it helps! – kardaj Jun 16 '16 at 12:26

score 0 · Answer 2 · answered Jun 14 '16 at 21:20

Maybe try something like this? Instead return sql write to file.

params.py file

def var1():
   return = '1,2,3'

def var2():
   return  '('abc', 'cde', 'xyz')'

rawsql.py

import params

def sqlCont():

  return "select * from emp.ids  where ids in '%s' and names in '%s' "   % (params.var1, params.var2 )

if __name__ == '__main__':
  sqlCont()

parameters substitution from one .py file to another .py file

2 Answers2