Skip to content

Instantly share code, notes, and snippets.

@brucevanhorn2
Created February 9, 2023 01:17
Show Gist options
  • Save brucevanhorn2/740dbe4c025cd0c7cf4c3ed36fec8a79 to your computer and use it in GitHub Desktop.
Save brucevanhorn2/740dbe4c025cd0c7cf4c3ed36fec8a79 to your computer and use it in GitHub Desktop.
SQL Server Connectivity Test Script

SQL Server Database Connectivity Tester

OK so you just made a SQL database somewhere... A VM, in the cloud, whatever. Then you made an app server somewhere else and you want to make sure the app server vm can actually access the database. This little script tests the ability to connect, do some basic CRUD, then cleans up after itself.

This is helpful if you run into problems and you need to rule out connectivity to the database.

You need Python3 and pymssql to make it work.

from pymssql import _mssql
server = "<db-server-here>"
user = "<db-user-here>"
password = "<db-password-here>"
database = "<db-name-here>"
# boosted thsi from the pymssql docs
conn = _mssql.connect(server=server, user=user, password=password, database=database)
conn.execute_non_query('CREATE TABLE persons(id INT, name VARCHAR(100))')
conn.execute_non_query("INSERT INTO persons VALUES(1, 'John Doe')")
conn.execute_non_query("INSERT INTO persons VALUES(2, 'Jane Doe')")
conn.execute_query("SELECT * FROM persons")
for row in conn:
print ("ID=%d, Name=%s" % (row['id'], row['name']))
conn.execute_non_query('DROP TABLE persons')
print("If you got no errors, we were able to do basic CRUD")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment