Inline Variable Substitution for Dynamic Database Connection String

567
3
Jump to solution
01-06-2023 10:57 AM
ABishop
MVP Regular Contributor

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

Amanda Bishop, GISP
0 Kudos
1 Solution

Accepted Solutions
RhettZufelt
MVP Frequent Contributor

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.

R_

View solution in original post

3 Replies
RhettZufelt
MVP Frequent Contributor

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.

R_

ABishop
MVP Regular Contributor

Thank you R_!

I'll try it and get back with you.

Amanda Bishop, GISP
0 Kudos
ABishop
MVP Regular Contributor

Thank you @RhettZufelt !  This worked beautifully. 😁

Amanda Bishop, GISP
0 Kudos