help fixing a Query using string formating

[ApoorvaDesai]

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)

stn_name = whatever
xf_name =  whatever2
 
Query_MDB_grid1 ="""SELECT EMS_StationName, EMS_XFName,           EMS_XFMRName, FromBusName, FromKV, ToBusName, ToKV, CKT,firstBusnumber, secondbusnumber  
                                FROM MarketDBTransformers 
                                WHERE EMS_StationName like ' """
 
Query_MDB_grid1 = Query_MDB_grid1 + stn_name + "'" + " and EMS_XFName like '"  
 
Query_MDB_grid1 = Query_MDB_grid1 + xf_name + "'"
 

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+" '

Query_MDB_grid1 ="""SELECT EMS_StationName, EMS_XFName, EMS_XFMRName, FromBusName, FromKV, ToBusName, ToKV, CKT,firstBusnumber, secondbusnumber  
                                FROM MarketDBTransformers 
                                WHERE EMS_StationName like '"+stn_name+"' and EMS_XFName like '"+xf_name+"' """
 

I really appreciate the help

[bartonc]

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.

[bartonc]

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":

stn_name = 'Nevada'
xf_name =  'ME'
 
Query_MDB_grid1 ="""SELECT EMS_StationName, EMS_XFName,           EMS_XFMRName, FromBusName, FromKV, ToBusName, ToKV, CKT,firstBusnumber, secondbusnumber  
                                FROM MarketDBTransformers 
                                WHERE EMS_StationName like ' """
 
Query_MDB_grid1 = Query_MDB_grid1 + stn_name + "'" + " and EMS_XFName like '"  
 
Query_MDB_grid1 = Query_MDB_grid1 + xf_name + "'"
print Query_MDB_grid1
 
 
Query_MDB_grid1 ="""SELECT EMS_StationName, EMS_XFName, EMS_XFMRName, FromBusName, FromKV, ToBusName, ToKV, CKT,firstBusnumber, secondbusnumber  
                                FROM MarketDBTransformers 
                                WHERE EMS_StationName like '%s' and EMS_XFName like '%s' """ %(stn_name, xf_name)
print Query_MDB_grid1

[bartonc]

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:

def MySQLSelect(table, arglist=(), argdict={}, **kwargs):
    """Build an SQL SELECT command from the arguments:
    Return a single string which can be 'execute'd.
    arglist is a list of strings that are column names to get.
    argdict and kwargs are two way to evaluate 'colName'=value
    for the WHERE clause"""
    a = ', '.join(arg for arg in arglist)
    args = argdict.copy()
    args.update(kwargs)
    for key, value in args.items():
        args[key] = (str(value), repr(value))[isinstance(value, str)]
    b = ''
    if args:
        b = 'WHERE %s' % ' AND '.join(key + '=' + value
                                      for key, value in args.items())
 
    return ' '.join(['SELECT', (a or '*'), 'FROM', table, b])
 
columns = ('EMS_StationName', 'EMS_XFName', 'EMS_XFMRName', 'FromBusName',
           'FromKV', 'ToBusName', 'ToKV', 'CKT', 'firstBusnumber',
           'secondbusnumber')
table = 'MarketDBTransformers'
 
where = """WHERE EMS_StationName like '%s' and EMS_XFName like '%s' """ %(stn_name, xf_name)
 
print MySQLSelect(table, columns) + where

[bartonc]

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.

[ApoorvaDesai]

Sorry for not using the code tags....will keep it in mind next time....thanks for the replies

[bartonc]

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.