473,382 Members | 1,529 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,382 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 1463
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...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.