Skip to content

Instantly share code, notes, and snippets.

Created February 9, 2023 01:17
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
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