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
6 1462
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.
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
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
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.
Sorry for not using the code tags....will keep it in mind next time....thanks for the replies
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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 , ;
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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"....
|
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...
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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)...
| |