(arcpy) Multiple TableSelect_analysis?

[JaredPilbeam2]

TableSelect_analysis(in_table, out_table, {where_clause})‍‍‍‍

"Selects table records matching a Structured Query Language (SQL) expression and writes them to an output table."

Using this function I have this simple script:

import arcpy
from arcpy import env

env.workspace = r'\\gisfile\GISstaff\Jared\ModelBuilder\JaredTest.gdb'

#variables
intable = r"\\gisfile\GISstaff\Jared\ModelBuilder\JaredTest.gdb\ElectionResults_Nov2016"
outtable = r"\\gisfile\GISstaff\Jared\ModelBuilder\JaredTest.gdb\Results3"
where_clause = "ContestTitle = 'BALLOTS CAST - TOTAL'"

arcpy.TableSelect_analysis(intable, outtable, where_clause)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

It selects all records named 'BALLOTS CAST - TOTAL' from the 'ContestTitle' column of my 'ElectionResults_Nov2016' table and writes them in the newly created 'Results3' table. 'BALLOTS CAST - TOTAL' is only one of 58 separate table record names. How can I write all 58 to their own tables?

Thanks for any help.

[RandyBurton]

Perhaps something like:

tbList = []

for row in arcpy.da.SearchCursor(intable, "ContestTitle"):
    if row[0] not in tbList:
        tbList.append(row[0])

for tbls in tbList:
    where_clause = "ContestTitle = '{}'".format(tbls)

    #  Note - you will need to adjust "tbls" to be a legal table name
    #  no spaces, symbols, length, etc
    tbls_fmt = #something to format table name here#

    arcpy.TableSelect_analysis(intable, tbls_fmt, where_clause)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[RandyBurton]

A dictionary version would also work:

import arcpy
from arcpy import env
env.workspace = r"Path\to\filedatabase.gdb"

intable = "ElectionResults_Nov2016"
# outtable will be named Results##

# you can manually create a dictionary
tblDict = {'BALLOTS CAST - TOTAL': 'Results1',
           'BALLOTS CAST - DISTRICT': 'Results2' }
for k, v in tblDict.iteritems():
    print k, v

# or automatically create one by scanning the intable
tblDict = {} # empty dictionary
results = 1 # counter for output tables

for row in arcpy.da.SearchCursor(intable, "ContestTitle"):
    if row[0] not in tblDict.keys():
        tblDict[row[0]] = "Results{}".format(results)
        results += 1 # increment results counter

# create the new tables
for k, v in tblDict.iteritems():
    where_clause = "ContestTitle = '{}'".format(k)
    print "Processing table '{}' where: {}".format(v, where_clause)
    arcpy.TableSelect_analysis(intable, v, where_clause)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JaredPilbeam2]

Randy,

Thanks for the help. All options are great. I went with the last one. I copied the script verbatim and it wrote new tables successfully except for one table, Results31. It's hung up. I did a few things to troubleshoot this: closed everything, used the 'ElectionResults_Nov2016' file in a different workspace, made sure the 'STATES ATTORNEY' record was even there...

Traceback (most recent call last):
  File "C:\Python27\ArcGIS10.4\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 326, in RunScript
    exec codeObject in __main__.__dict__
  File "\\gisfile\GISstaff\Jared\Python Scripts\Test2.py", line 22, in <module>
    arcpy.TableSelect_analysis(intable, v, where_clause)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.4\ArcPy\arcpy\analysis.py", line 145, in TableSelect
    raise e
ExecuteError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used. [ElectionResults_Nov2016]
An invalid SQL statement was used. [SELECT * FROM ElectionResults_Nov2016 WHERE ContestTitle = 'STATE'S ATTORNEY                                        ']
The table was not found. [Results31]
Failed to execute (TableSelect).‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

**EDIT: the 'STATE'S ATTORNEY' record is the only one with an apostrophe. So, I'm guessing this may be a format issue. I'm attempting to use the substitution function to get rid of it.

import re
re.sub() ‍‍‍‍

Also, instead of naming the new tables 'Results' how would you give them the table record's name?

[RandyBurton]

For table names from the field data, you would need to replace anything that wasn't a letter, number or underscore.  As an alternate, you might take the last word in the field data by using split on the last space.

tbls_fmt = tbls.split(" ")[:-1]‍‍

The error you mentioned occurred when a single quote was not escaped in the where clause:

where = "SELECT * FROM ElectionResults_Nov2016 WHERE ContestTitle = 'STATE'S ATTORNEY'"

# should be
where = "SELECT * FROM ElectionResults_Nov2016 WHERE ContestTitle = 'STATE''S ATTORNEY'"

flds = "STATE'S ATTORNEY" # single quote inside double quotes, escape not needed

ContestTitle = flds.replace("'", "''") # for file geodatabase, use 2 single quotes‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Hope this helps.

EDIT:  When using a where statement to find a value in a file geodatabase with an apostrophe, escape the single quote by adding a second one.

[RandyBurton · Accepted solution]

UPDATED: I did a quick test of this, so it should work.  For a file geodatabase, to match a single quote in a field, you escape the single quote with a second one (not \' );  I've made a correction in the code.  It should now replace the single quote in the where_clause with an escaped one and remove any non-alphanumeric characters, etc. for table name.  I also added a couple of lines (20-21) to delete tables if found; you may or may not want to do this.

import arcpy
from arcpy import env
import re

env.workspace = r"Path\to\filedatabase.gdb"

intable = "ElectionResults_Nov2016"

tbList = []

for row in arcpy.da.SearchCursor(intable, "ContestTitle"):
    if row[0] not in tbList:
        tbList.append(row[0])

for tbl in tbList:
    where_clause = "ContestTitle = '{}'".format(tbl.replace("'","''"))

    tbl_fmt = ' '.join(re.sub('[^0-9a-zA-Z ]', '', tbl.strip()).split()).replace(" ","_")

    if arcpy.Exists(tbl_fmt):
        arcpy.Delete_management(tbl_fmt) # this will delete the table if it exists

    arcpy.TableSelect_analysis(intable, tbl_fmt, where_clause)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JaredPilbeam2]

Randy,

It works pretty flawlessly, thanks! Could you briefly explain how the dictionary works in this script?

tbList = []‍

[RandyBurton]

In the last version a list was used, not a dictionary, as the goal was to use field data in creating a table name.  A dictionary works with a key and value (like the traditional dictionary links a word and definition).  But it is also possible to create a dictionary while scanning the field data as was done in the second example.  Code (untested) would look something like:

tblDict = {} # empty dictionary

for row in arcpy.da.SearchCursor(intable, "ContestTitle"):
    if row[0] not in tblDict.keys():
        tblDict[row[0]] = ' '.join(re.sub('[^0-9a-zA-Z ]', '', row[0].strip()).split()).replace(" ","_")‍‍‍‍‍

So, using either a list or dictionary would work.  In this case a list seemed a bit simpler.  Hope this helps.