As of Pro 2.5.1 arcpy.ListDatabaseSequences() only works for fgdb. I'd like to deploy them on an EGDB and would rather use the arcpy method than a t-sql approach
I got this from ESRI Tech Support:
ArcSDESQLExecute—ArcGIS Pro | Documentation
Based on that, I created a python script that looks like thes:
<SPAN class="keyword token">import</SPAN> arcpy egdb <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'\\path\to\sde_connectionFile.sde'</SPAN> egdb_conn <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ArcSDESQLExecute<SPAN class="punctuation token">(</SPAN>egdb<SPAN class="punctuation token">)</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">''' select * from sys.sequences '''</SPAN> egdb_return <SPAN class="operator token">=</SPAN> egdb_conn<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN>sql<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> egdb_return<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'{}: {}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>i<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
It tosses an error for me:
Traceback (most recent call last): File "<ipython-input-1-516d1c09b234>", line 10, in <module> egdb_return = egdb_conn.execute(sql) File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\arcobjects\arcobjects.py", line 43, in execute return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args))) AttributeError: ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65 Invalid pointer argument to function<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
I suspect I need the actual sql db name and instance, but I have to get that from the IT-DB admin guy. I'll see if that fixes it once I do.
I agree this should be available in arcpy.
In the meantime, this works for SQL Server:
sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"select name from sys.objects where type_desc='SEQUENCE_OBJECT'"</SPAN> sql_exe <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ArcSDESQLExecute<SPAN class="punctuation token">(</SPAN>r<SPAN class="string token">"path\to\connection.sde"</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> s_name <SPAN class="keyword token">in</SPAN> sql_exe<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN>sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>s_name<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
More stuff to search for:
sys.objects (Transact-SQL) - SQL Server | Microsoft Docs
EDIT:
The above only returns the sequence names. If you want to get things like the current value, you have to use sys.sequences. And some queries on that throw errors in arcpy.
sys.sequences (Transact-SQL) - SQL Server | Microsoft Docs
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"start_value"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"increment"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"minimum_value"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"maximum_value"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"is_cycling"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"is_cached"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"cache_size"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"system_type_id"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"user_type_id"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"precision"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"scale"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"current_value"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"is_exhausted"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"last_used_value"</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> fields<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN> result <SPAN class="operator token">=</SPAN> sql_exe<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"select {} from sys.sequences"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"{}:\tsucceeded"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">except</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"{}:\tfailed"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># start_value: failed</SPAN> <SPAN class="comment token"># increment: failed</SPAN> <SPAN class="comment token"># minimum_value: failed</SPAN> <SPAN class="comment token"># maximum_value: failed</SPAN> <SPAN class="comment token"># is_cycling: succeeded</SPAN> <SPAN class="comment token"># is_cached: succeeded</SPAN> <SPAN class="comment token"># cache_size: succeeded</SPAN> <SPAN class="comment token"># system_type_id: succeeded</SPAN> <SPAN class="comment token"># user_type_id: succeeded</SPAN> <SPAN class="comment token"># precision: succeeded</SPAN> <SPAN class="comment token"># scale: succeeded</SPAN> <SPAN class="comment token"># current_value: failed</SPAN> <SPAN class="comment token"># is_exhausted: succeeded</SPAN> <SPAN class="comment token"># last_used_value: failed</SPAN> <SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
If it fails on any of the *_value fields, there isn't any value in it for me. I've tried several different iterations and it fails each time. I may try something from SSMS, but I don't think the security team is gonna let be do a back door approach like that...
Come on ESRI: help us out here....
In Sql Server Management Studio it's pretty easy:
select name,start_value,increment,current_value from YOUR_DATABASE_NAME.sys.sequences<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN></SPAN>
I tried using pydobc to connect but there must be a security issue with the particular database; SSMS is fine but pyodbc won't/can't make a connection.
Joe is this all related to your post I encountered from Jan-2020. Joe's post
What is your DB platform, I'm curious to know what others are using. I'm using Oracle.
I mentioned in a reply to your Jan Post, that I am just now having issues with my Unique ID auto-incremental column. I am looking for solutions.
Ray
In SQL Server Management Studio (SSMS), you can find the list of sequences by connecting to the database instance > Database > Programmability > Sequences > and the sequences are listed there.
I figured out what was causing the error being thrown for certain columns in `sys.sequences` - the columns seem to be in some datatype that the ArcPy SQL interface can't handle. I ended up casting them to VARCHAR in my query and then just converting them back to integer later in my script. So this would give you everything you need to create the same sequence again for example:
def get_sequence_info(sde: str) -> list[dict]: cols = ['seq_name', 'seq_start_id', 'seq_inc_value', 'current_value'] sql = 'SELECT name, CAST(start_value AS VARCHAR(100)), CAST(increment AS VARCHAR(100)), CAST(current_value AS VARCHAR(100)) FROM sys.sequences' sql_exe = arcpy.ArcSDESQLExecute(sde) sequences = sql_exe.execute(sql) if sequences is True: return [] else: rules = [] for rule in sequences: rules.append( {i[0]: int(i[1]) if i[1].isdigit() else i[1] for i in zip(cols, rule)} ) return rules
Meld je aan om te posten, content te volgen en meer. Nieuw hier? Registreer gratis.
