471,334 Members | 1,527 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,334 software developers and data experts.

help fixing a Query using string formating

just for info I am using BOA constructor and python 2.3

I am writting a query which works if i break it up as follows (and it also works if I run it in SQL view of access)
Expand|Select|Wrap|Line Numbers
  1. stn_name = whatever
  2. xf_name =  whatever2
  3.  
  4. Query_MDB_grid1 ="""SELECT EMS_StationName, EMS_XFName,           EMS_XFMRName, FromBusName, FromKV, ToBusName, ToKV, CKT,firstBusnumber, secondbusnumber  
  5.                                 FROM MarketDBTransformers 
  6.                                 WHERE EMS_StationName like ' """
  7.  
  8. Query_MDB_grid1 = Query_MDB_grid1 + stn_name + "'" + " and EMS_XFName like '"  
  9.  
  10. Query_MDB_grid1 = Query_MDB_grid1 + xf_name + "'"
  11.  
But if I try to combine the entire query as follows it does not see stn_name and
xf_name as variable but as text within quotes ' " + xf_name+" '
Expand|Select|Wrap|Line Numbers
  1. Query_MDB_grid1 ="""SELECT EMS_StationName, EMS_XFName, EMS_XFMRName, FromBusName, FromKV, ToBusName, ToKV, CKT,firstBusnumber, secondbusnumber  
  2.                                 FROM MarketDBTransformers 
  3.                                 WHERE EMS_StationName like '"+stn_name+"' and EMS_XFName like '"+xf_name+"' """
  4.  
I really appreciate the help
Oct 17 '07 #1
6 1272
bartonc
6,596 Expert 4TB
And I will appreciate your using CODE tags. Instructions on their use is on the right hand side, every time you post. Thanks.

(I have to add these manually when you neglect using them.
There is a site rule requiring their use; see our Posting Guidelines) Thank you.
Oct 17 '07 #2
bartonc
6,596 Expert 4TB
String formating is the answer. String formats use the % operator to show which variable (and its type - in this case %s means string type) goes into the string when it is "formated":
Expand|Select|Wrap|Line Numbers
  1. stn_name = 'Nevada'
  2. xf_name =  'ME'
  3.  
  4. Query_MDB_grid1 ="""SELECT EMS_StationName, EMS_XFName,           EMS_XFMRName, FromBusName, FromKV, ToBusName, ToKV, CKT,firstBusnumber, secondbusnumber  
  5.                                 FROM MarketDBTransformers 
  6.                                 WHERE EMS_StationName like ' """
  7.  
  8. Query_MDB_grid1 = Query_MDB_grid1 + stn_name + "'" + " and EMS_XFName like '"  
  9.  
  10. Query_MDB_grid1 = Query_MDB_grid1 + xf_name + "'"
  11. print Query_MDB_grid1
  12.  
  13.  
  14. Query_MDB_grid1 ="""SELECT EMS_StationName, EMS_XFName, EMS_XFMRName, FromBusName, FromKV, ToBusName, ToKV, CKT,firstBusnumber, secondbusnumber  
  15.                                 FROM MarketDBTransformers 
  16.                                 WHERE EMS_StationName like '%s' and EMS_XFName like '%s' """ %(stn_name, xf_name)
  17. print Query_MDB_grid1
Oct 17 '07 #3
bartonc
6,596 Expert 4TB
I've published a set of SQL helper functions in the Articles section which make query formation a lot more readable and much easier to change. For example:
Expand|Select|Wrap|Line Numbers
  1. def MySQLSelect(table, arglist=(), argdict={}, **kwargs):
  2.     """Build an SQL SELECT command from the arguments:
  3.     Return a single string which can be 'execute'd.
  4.     arglist is a list of strings that are column names to get.
  5.     argdict and kwargs are two way to evaluate 'colName'=value
  6.     for the WHERE clause"""
  7.     a = ', '.join(arg for arg in arglist)
  8.     args = argdict.copy()
  9.     args.update(kwargs)
  10.     for key, value in args.items():
  11.         args[key] = (str(value), repr(value))[isinstance(value, str)]
  12.     b = ''
  13.     if args:
  14.         b = 'WHERE %s' % ' AND '.join(key + '=' + value
  15.                                       for key, value in args.items())
  16.  
  17.     return ' '.join(['SELECT', (a or '*'), 'FROM', table, b])
  18.  
  19. columns = ('EMS_StationName', 'EMS_XFName', 'EMS_XFMRName', 'FromBusName',
  20.            'FromKV', 'ToBusName', 'ToKV', 'CKT', 'firstBusnumber',
  21.            'secondbusnumber')
  22. table = 'MarketDBTransformers'
  23.  
  24. where = """WHERE EMS_StationName like '%s' and EMS_XFName like '%s' """ %(stn_name, xf_name)
  25.  
  26. print MySQLSelect(table, columns) + where
Oct 17 '07 #4
bartonc
6,596 Expert 4TB
I've published a set of SQL helper functions in the Articles section which make query formation a lot more readable and much easier to change.
Looking at that, though, I see that they need updating to the current versions which support the conversion of None to NULL in inserts and updates.
Oct 17 '07 #5
Sorry for not using the code tags....will keep it in mind next time....thanks for the replies
Oct 18 '07 #6
bartonc
6,596 Expert 4TB
Looking at that, though, I see that they need updating to the current versions which support the conversion of None to NULL in inserts and updates.
I have updated the SQL helpers in this thread. And my SQL Connection Manager has been updated here.
Oct 18 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by stevenrec | last post: by
17 posts views Thread by so many sites so little time | last post: by
8 posts views Thread by Lucky | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.