topic Re: Build a query string in Python in Transportation Questions

Build a query string in Python

Anonymous User — Tue, 12 Mar 2013 01:58:53 GMT

Original User: chent

Hi, I am trying to write a small Python script to build a query string so ArcGIS can use it to select data by attribute.
In this script it first reads a text file (attachments) with 5 lot/plan details (each in separate line), then build a single line query string accordingly.

Here is my script to build the query string:
#===============
f_count = open("C:\GeoSpatialTraining\ArcGIS10\GISProgramming101\Exercises\Data\Local data\LotPlan_List.txt", "r")
#Count records number
numRec = len(f_count.readlines())
print "There are " + str(numRec) + " records in the file."

f = open("C:\GeoSpatialTraining\ArcGIS10\GISProgramming101\Exercises\Data\Local data\LotPlan_List.txt", "r")
sQuery = ""
i = 0

for rec in f:
    #print rec
    i = i + 1
    if i < numRec:
        sQuery = sQuery + "\"LOT_PLAN\" = '" + rec + "' OR "
    else:
        sQuery = sQuery + "\"LOT_PLAN\" = '" + rec + "'"
    #print sQuery
f.close()
print sQuery

#======================

This is what I'd expect as a result:

"LOT_PLAN" = '68SP245201' OR "LOT_PLAN" = '7SP101558' OR "LOT_PLAN" = '8RP620660' OR "LOT_PLAN" = '8SP239672' OR "LOT_PLAN" = '9SP239672'

But it ends up like this:

"LOT_PLAN" = '68SP245201
' OR "LOT_PLAN" = '7SP101558
' OR "LOT_PLAN" = '8RP620660
' OR "LOT_PLAN" = '8SP239672
' OR "LOT_PLAN" = '9SP239672
'

Can anyone help please? Thanks.

Re: Build a query string in Python

DanPatterson_Retired — Tue, 12 Mar 2013 02:42:07 GMT

reformat your code so that indentations are readily read

Re: Build a query string in Python

Anonymous User — Tue, 12 Mar 2013 02:49:19 GMT

Original User: chent

reformat your code so that indentations are readily read

Thanks for the reply.
The indentations seem fine in my script, it's only when I paste the code in the message box here that all indentations were removed.

I have attached the script in my first message, can you run it and see if it comes up with different result please? Thanks.

Re: Build a query string in Python

MathewCoyle — Sun, 12 Dec 2021 06:37:18 GMT

Something like this should work for you.

import os

file = r'C:\GeoSpatialTraining\ArcGIS10\GISProgramming101\Exercises\Data\Local data\LotPlan_List.txt'
f_count = open(file, "r")
#Count records number
numRec = len(f_count.readlines())
print("There are {0} records in the file.".format(numRec))
f = open(file, "r")
field = 'LOT_PLAN'
lots_list = []
for rec in f:
    rec = rec.rstrip('\n')
    lots_list.append(rec)
sQuery = '''"{0}" in ('{1}')'''.format(field, "', '".join(lots_list))
f.close()
print(sQuery)

Re: Build a query string in Python

Anonymous User — Tue, 12 Mar 2013 19:39:02 GMT

Original User: recurvata

The indentations seem fine in my script, it's only when I paste the code in the message box here that all indentations were removed.

The indentations may be fine in your script, but it's helpful if you use code tags when posting code on the forum. Click the # symbol in the icon menu above the post entry form and paste your code in between the code tags. Or type the code tags. See pngs below. Besides allowing someone to check for an error there, it's much more legible.

Re: Build a query string in Python

curtvprice — Sun, 12 Dec 2021 06:37:21 GMT

The indentations seem fine in my script, it's only when I paste the code in the message box here that all indentations were removed

Please read:
[thread]48475[/thread]

Here's my attempt at a solution for you -- just to show your original syntax could work too. (I believe if the table is indexed by this field, the form may be slightly faster -- indexes are data-format dependent though so I'm not sure.)

queries = []
qry = '"LOT_PLAN" = \'{0}\''
for rec in f:
    queries.append(qry.format(rec))
f.close()
sQuery = " OR ".join(queries)
print sQuery

Re: Build a query string in Python

Anonymous User — Sun, 12 Dec 2021 06:37:23 GMT

Original User: chent

Thanks for that. I put your code in and run it, but it still comes up the same result.
Here is the updated code:

f_count = open("C:\GeoSpatialTraining\ArcGIS10\GISProgramming101\Exercises\Data\Local data\LotPlan_List.txt", "r")
#Count records number
numRec = len(f_count.readlines())
print "There are " + str(numRec) + " records in the file."

f = open("C:\GeoSpatialTraining\ArcGIS10\GISProgramming101\Exercises\Data\Local data\LotPlan_List.txt", "r")
sQuery = ""
queries = []
qry = '"LOT_PLAN" = \'{0}\''
for rec in f:
    queries.append(qry.format(rec))
f.close()
sQuery = " OR ".join(queries)
print sQuery

Any idea?

Re: Build a query string in Python

LindseyWood — Sun, 12 Dec 2021 06:37:27 GMT

How about something like this? 😄

import string

##Variables separated "find it easier to work with when doing query strings"
lot_plan = 'LOT_PLAN'
equal = '='
recs = ['ab1','ab2','ab3','ab4']

##Set up string by concatenating each part
begQuery = '\"%s" %s ' %(lot_plan,equal)

## Empty list to hold values
query = []

##May not need this but put it in anyway
numRecs = len(recs)
print numRecs

for rec,i in zip(recs,range(numRecs)):
    totQuery = "%s '%s' OR" %(begQuery,rec)
    print totQuery
    query.append(totQuery)
    
## Then if you need it all in one string
x = ' '.join(str(s) for s in query)
print x

Of course apply it to your stuff the output it gives me is
4
"LOT_PLAN" = 'ab1' OR
"LOT_PLAN" = 'ab2' OR
"LOT_PLAN" = 'ab3' OR
"LOT_PLAN" = 'ab4' OR
"LOT_PLAN" = 'ab1' OR "LOT_PLAN" = 'ab2' OR "LOT_PLAN" = 'ab3' OR "LOT_PLAN" = 'ab4' OR

At the end you would need to cut off the OR or have a differnt
variable or some sort of catch that if its the end of the record count use that
Hope this helped

Re: Build a query string in Python

Anonymous User — Sun, 12 Dec 2021 06:37:29 GMT

Original User: curtvprice

Thanks for that. I put your code in and run it, but it still comes up the same result.
Here is the updated code:Any idea?

Yes - when you read records that way, the newline "\n" is included in rec.

>>> f = open("log")
>>> for rec in f:
...   rec
...
'201211291133   0     0     0cprice ap\n'
'201211291133   0     0     0cprice ae\n'

I suggest using the .strip() string function to remove whitespace around your text record, including newline:

for rec in f:
    queries.append(qry.format(rec.strip()))

Re: Build a query string in Python

TerenceChen — Sun, 12 Dec 2021 06:37:32 GMT

Thanks for that, the strip() function does work.
Here is the updated script:

f_count = open("C:\GeoSpatialTraining\ArcGIS10\GISProgramming101\Exercises\Data\Local data\LotPlan_List.txt", "r")
#Count records number
numRec = len(f_count.readlines())
print "There are " + str(numRec) + " records in the file."

f = open("C:\GeoSpatialTraining\ArcGIS10\GISProgramming101\Exercises\Data\Local data\LotPlan_List.txt", "r")
sQuery = ""
i = 0

for rec in f:
    #print rec
    i = i + 1
    if i < numRec:
        sQuery = sQuery + "\"LOT_PLAN\" = '" + rec.strip() + "' OR " 
    else:
        sQuery = sQuery + "\"LOT_PLAN\" = '" + rec.strip() + "' "
    #print sQuery
f.close()
print sQuery