473,287 Members | 1,813 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 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 1462
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

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

Similar topics

3
by: Dan Gidman | last post by:
Okay all I have a problem. I have two list of adresses and phone numbers. I do not have control over the contents of the lists The only unique field between the two is the phone number. I need...
5
by: Alicia | last post by:
Hello everyone based on the data, I created a union query which produces this. SELECT ,,, 0 As ClosedCount FROM UNION SELECT ,, 0 AS OpenedCount, FROM ORDER BY , ;
12
by: strict9 | last post by:
Hello all, I'm writing several queries which need to do various string formating, including changing a phone number from (123) 456-7890. After some problem with data mismatches, I finally got it...
1
by: stevenrec | last post by:
I'm back. Do not use queries too often and have trouble formating a field: misc: if((!)='2';"Normal)"; if((!)='8';"Extra") Basically want it to check the number in the <order_typefield and if...
17
by: so many sites so little time | last post by:
all right so the script is pretty simple it goes it retrives what the id of the post is and it lets you edit it well no it doesnt. now if you go to www.kirewire.com/pp2/index/php you will see a...
8
by: Lucky | last post by:
hi guys! back again with another query. the problem is like this. i want to print a line like this: "---------------------------------------------" the easiest way is to simply assign it to...
9
by: JAF | last post by:
I need help with the following format: 1. Paragragh goes here and text wraps or indents several spaces on second and subsequent lines. 2. Paragragh goes here and text wraps or indents...
1
by: Randy Volkart | last post by:
I'm trying to fix a glitch in a complex access database, and have a fairly complex problem... unless there's some obscure easy fix I don't know being fairly new with Access. Basically, the area...
2
by: sitko | last post by:
Hi, I'm in the process of converting a VB.net program into a C program so it can run on a unix like machine. I've been moving along at a nice pace, but this conversion has stumped me. I need...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.