Inline Variable Substitution for Dynamic Database Connection String

ABishop — Fri, 06 Jan 2023 18:57:17 GMT

Hopefully I am posting this in the right place and this makes sense to somebody who can assist me...

I wrote a series of python scripts which extracts data tables from views in our CAMA database. Our CAMA database rolls over each year with a new year and consequentially the year has to be updated in the database connection string and view name. This wouldn't be so bad if there weren't 30+ lines in each script and approx. 10 scripts that have year-dependent values.

I have looked into inline subs using variables and attempted a couple of different methods. I have pasted one test script of a view extraction example below. Could somebody review it and assist me with where I am going wrong? (Note: I am using ArcGIS Pro 3.0.3, ArcGIS Server 10.8, and Enterprise geodatabase in MS SQL Server version 14.0.1000.169)

# Process: import arcpy modules import arcpy, os, sys from arcpy import env from datetime import datetime import ctypes ctypes.windll.kernel32.SetConsoleTitleW('02_ExtractViews') env.workspace = r"K:\GIS_UPDATE\DATA\EXTRACTVIEWS" env.overwriteOutput = True # Process: start timer startTime = time.perf_counter() # Process: set local variables fieldmap = "" RollYear = "2023" AddressExtractView = r"K:\GIS_TOOLS\DB\CAMA(%RollYear%).sde\(%RollYear%).dbo.AddressExtractView(%RollYear%)" gdb = r"K:\GIS_UPDATE\DATA\EXTRACTVIEWS\EXTRACTVIEWS.gdb" # Process: copying AddressExtract View to Address1 table in EXTRACTVIEWS.gdb arcpy.conversion.TableToTable(AddressExtractView, gdb, "Address1", '', fieldmap, '') # Process: end timer endTime = time.perf_counter() elapsedTime = round((endTime - startTime) / 60, 2) print("Script finished in {0} minutes".format(elapsedTime)) # Process: date and time stamp date = datetime.now().strftime("%Y_%m_%d-%I:%M:%S_%p") print("Date and time stamp: "+f"{date}") # Process: provide input to stop script - uncomment input below if you need to see the script final output in the command window input('Press ENTER to exit')

Thank you in advance,

Amanda

Re: Inline Variable Substitution for Dynamic Database Connection String

RhettZufelt — Fri, 06 Jan 2023 19:22:32 GMT

For variable substitution in the AddressExtractView string? You can use f string substitution like such:

AddressExtractView = fr"K:\GIS_TOOLS\DB\CAMA{RollYear}.sde\{RollYear}.dbo.AddressExtractView{RollYear}"

If I understand the question correctly.

Re: Inline Variable Substitution for Dynamic Database Connection String

ABishop — Fri, 06 Jan 2023 19:25:42 GMT

Thank you R_!

I'll try it and get back with you.

Re: Inline Variable Substitution for Dynamic Database Connection String

ABishop — Fri, 06 Jan 2023 19:28:12 GMT

Thank you @RhettZufelt ! This worked beautifully. 😁

topic Re: Inline Variable Substitution for Dynamic Database Connection String in Geoprocessing Questions

Inline Variable Substitution for Dynamic Database Connection String

Re: Inline Variable Substitution for Dynamic Database Connection String

Re: Inline Variable Substitution for Dynamic Database Connection String

Re: Inline Variable Substitution for Dynamic Database Connection String