Trying to use arcpy find duplicates in layers from mxd.

[StudentFernie]

Basically i want to find duplicate records from my attribute tables in my mxd not my data base because my mxd. has several definition query's i would like to preserve. I am essentially trying to script this query  

[FIELD_NAME] In (SELECT [FIELD_NAME] FROM [TABLE_NAME] GROUP BY [FIELD_NAME] HAVING Count(*)>1 )

Any tips as to why this wont cooperate? I either get a cannot combine string and layer objects error or an invalid query error because it says my layer does not exist because it wont find the table related to my mxd. 

Cheers,  

# Set workspace environments. Workspace must be a connection file to an ADMIN (sde) connection
arcpy.env.workspace = r"\\fs\GIS\admin\temp_connect_files\COF_temp.sde"
arcpy.env.overwriteOutput = True
print "workspace environment connected"
# Set workspace variable
workspace = arcpy.env.workspace

# Local variables:

mxd = arcpy.mapping.MapDocument(r"P:\Joey_Plessis\service_cards\pdf_map_automation_script\DEFAULT_bc_one_call.mxd")

df = arcpy.mapping.ListDataFrames(mxd,"*")[0] #DONT WORRY ABOUT THIS PART

layer = arcpy.mapping.ListLayers(mxd, raw_input("enter layer name"), df)[0]

field = raw_input("enter field name")

print layer
print field
where = field +" " + "In" + " " + "(SELECT" +" "+ field + " "+ "FROM" +" " + "layer" + " "+ "GROUP BY" +" " + field +" "+ "HAVING Count(*)>1 )"
print where

arcpy.SelectLayerByAttribute_management(layer, "NEW_SELECTION" ,where)

[JoshuaBixby]

Firstly, it would be helpful to community members if you formatted the code in your post: /blogs/dan_patterson/2016/08/14/script-formatting?sr=search&searchId=02fa3b47-d128-40af-9c35-fb69e3a...‌.  Secondly, I suggest you take a look at Python Format String Syntax.  The line where you are building your SQL WHERE statement is very difficult to read with the long string (no pun intended) of concatenations.

You say the code isn't cooperating, what are you expecting it to do and what is it actually doing?

[JoshuaBixby]

Student Fernie‌, I see you updated your original post with some more information.  In terms of code formatting, I was thinking more along the lines of:

# Set workspace environments. Workspace must be a connection file to an ADMIN (sde) connection
arcpy.env.workspace = r"\\fs\GIS\admin\temp_connect_files\COF_temp.sde"
arcpy.env.overwriteOutput = True
print "workspace environment connected"
# Set workspace variable
workspace = arcpy.env.workspace


# Local variables:

mxd = arcpy.mapping.MapDocument(r"P:\Joey_Plessis\service_cards\pdf_map_automation_script\DEFAULT_bc_one_call.mxd")


df = arcpy.mapping.ListDataFrames(mxd,"*")[0] #DONT WORRY ABOUT THIS PART


layer = arcpy.mapping.ListLayers(mxd, raw_input("enter layer name"), df)[0]


field = raw_input("enter field name")

print layer
print field
where = field +" " + "In" + " " + "(SELECT" +" "+ field + " "+ "FROM" +" " + "layer" + " "+ "GROUP BY" +" " + field +" "+ "HAVING Count(*)>1 )"
print where


arcpy.SelectLayerByAttribute_management(layer, "NEW_SELECTION" ,where)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

With regard to your first issue, i.e., "get a cannot combine string and layer objects error," you have already written the answer.  arcpy.mapping.ListLayers returns a list of ArcPy Mapping Layer objects.  You can't concatenate an object to a string, just like you can't concatenate an integer or float to a string, the data types don't support doing so:

>>> "string" + object()
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
TypeError: cannot concatenate 'str' and 'object' objects
>>> 
>>> "string" + 1
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
TypeError: cannot concatenate 'str' and 'int' objects
>>> 
>>> "string" + 1.
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
TypeError: cannot concatenate 'str' and 'float' objects
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

ArcPy Layer objects store their name in a property named "name", which you can concatenate to a string since the property returns a Unicode string.

>>> lyr
<map layer u'map layer'
>>> 
>>>> lyr.name
u'map layer'
>>> ‍‍‍‍‍
>>> "string " + lyr.name
u'string map layer'‍‍‍‍‍‍‍‍

[StudentFernie]

Ok got that error figured, now it wont point to the table.

thanks for your help!

Traceback (most recent call last):
 File "P:\Joey_Plessis\find_dupes_mxd_edition\find_dupes_mxd.py", line 54, in <module>
 print layer.name.table
AttributeError: 'unicode' object has no attribute 'table'
>>>

[JoshuaBixby]

You appear to be struggling with the basic structure of the ArcPy Mapping module.  I encourage you to spend some time reading the Introduction to arcpy.mapping—Help | ArcGIS Desktop .  Regarding your error message specifically, the name property of a layer object returns a Unicode string, as I mention above.  Python strings have no property or method named "table," hence the error message.  What you need to look into is either datasetName or dataSource properties of Layer—Help | ArcGIS Desktop.

[StudentFernie]

Look man I get my scripting sucks, I just need a solution. how do I point to my layer table so i can throw a variable into my query 

[JoshuaBixby]

As I mention above, you need to look into either datasetName or dataSource properties of Layer—Help | ArcGIS Desktop.  I am willing to help folks work through problems, and especially help them learn about Python and ArcPy, but I don't have the time to do other people's work for them, so I am done here.  Best of luck, maybe someone else will find the time to give you a solution.

[StudentFernie]

my apologies, what im trying to do is now link to the layers table so I can complete my query. 

[StudentFernie]

or like you know just say, replace layer.table with layer.datasetName..........

GOT IT NOW THANKS