When the Recipe Calls for SQL, Python, and PowerShell

I recently worked on a project using a mix of SQL, Python, and PowerShell. I figured stepping through that workflow would be worth a blog post so here we go!

Set the Stage

I needed a new Python script that also had some SQL scripting for a project and I knew I could use an existing Python script with some updated tweaks. We’ve all been there. CTRL-C, CTRL-V, a couple changes to the SQL running within the Python script, and we’re onto the next task.

Not so much in this case.

I tested the new Python script and was getting an error claiming:

“No results. Previous SQL was not a query.”

I double checked my SQL script within the Python script and made sure no obvious problems jumped out at me. I vaguely recognized running into this error before and ending up here when “SET NOCOUNT ON” was needed. NOCOUNT wasn’t an issue this time around.

Trim the Fat

The issue ended up being an extra line where I was printing the database name. I took out the line “PRINT @dbname” which wasn’t even necessary in the context of what I was doing and that ended up fixing the problem.

That fix was relatively easy. The frustrating part was that I was going to have to go back and make that change in a handful of files. I didn’t feel like opening each file, navigating to the right line, taking out the extra print statement, rinse, and repeat.

It’s Not That I’m Lazy

Instead, I opened up PowerShell and put together this little script to remove the line from each file. Something like this:

$StringToRemove = "`rPRINT @dbname;`r"

Get-ChildItem 'C:\Users\Chad\Desktop\TestFolder\*.txt' | ForEach {
     (Get-Content $_ -Raw) | ForEach  {$_ -Replace $StringToRemove, ""} | Set-Content $_
}

I included `r in the string to remove so that I wouldn’t have extra blank lines. That way I can go from something like this:

To something like this:

Onto the Next

There you go. It’s not every day I get to play around with SQL, Python, and PowerShell together. The example above isn’t anything too crazy but it’s a fun look into mixing things up.

Thanks for reading!