Connecting to Microsoft SQL Server Using Python

Connecting to Microsoft SQL server using Python

This is how I do it...

import pyodbc 
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=server_name;"
                      "Database=db_name;"
                      "Trusted_Connection=yes;")

cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Table')

for row in cursor:
    print('row = %r' % (row,))

Relevant resources:

• https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows

• http://blogs.msdn.com/b/cdndevs/archive/2015/03/11/python-and-data-sql-server-as-a-data-source-for-python-applications.aspx

Connecting to Microsoft SQL Server in Python

So in case anyone else runs into this issue in the future. pyodbc was not properly connecting to the driver ODBC Driver 17 for SQL Server. My fix was creating a User DSN after running the Windows executable odbcad32.exe. That properly identified the server, credentials and mirror server for the database. I called this User DSN 'sqlexpress' and the following is my connect string now.

cnxn_write = pyodbc.connect(r'DSN=sqlexpress')

Sadly this feels more like a workaround than a solution, but it now properly connects to my database.

Connecting Python to Remote SQL Server

Finally to answer my question, I had to use pymssql which worked. I did not have to put the port number which was making me confused. Thanks everyone for taking out time to answer.

import pymssql

conn = pymssql.connect(
    host=r'10.174.124.12',
    user=r'dom\user',
    password=r'password',
    database='db'
)
cursor = conn.cursor(as_dict=True)
cursor.execute('Select top 4 location_id, description from t_location with (nolock)')
data = cursor.fetchall()
data_df = pd.DataFrame(data)

cursor.close()

Connect to SQL Server in Python with ReadOnly

readonly=True is an attribute of pyodbc's connect() method, not a part of the connection string you pass to SQL Server. IMHO try just passing all the attributes instead of building a full connection string:

conn = pyodbc.connect(driver='{SQL Server}', host=<server>, database=<db>,
  trusted_connection='yes', user='', password='', readonly = True)

Or, like the answer you mentioned offered, use:

conn = pyodbc.connect('driver={ODBC Driver 17 for SQL Server};'
  + 'SERVER=...;DATABASE=...;'
  + 'UID=' + user + ';PWD=' + password + ';'
  + 'ApplicationIntent=ReadOnly')

Also, are you intentionally connecting explicitly to a secondary? Why don't you connect to the AG name and if your app is doing only read operations then the worst that happens (if, say, someone breaks read-only routing) is that those read operations happen on the primary. Your code should never connect to a specific physical host / cluster node and assume that will always be a read-only secondary... what happens if there is a failover tomorrow? Who's updating all the connection strings?

Connecting to MS SQL Server with Windows Authentication using Python?

You can specify the connection string as one long string that uses semi-colons (;) as the argument separator.

Working example:

import pyodbc
cnxn = pyodbc.connect(r'Driver=SQL Server;Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute("SELECT LastName FROM myContacts")
while 1:
    row = cursor.fetchone()
    if not row:
        break
    print(row.LastName)
cnxn.close()

For connection strings with lots of parameters, the following will accomplish the same thing but in a somewhat more readable way:

conn_str = (
    r'Driver=SQL Server;'
    r'Server=.\SQLEXPRESS;'
    r'Database=myDB;'
    r'Trusted_Connection=yes;'
    )
cnxn = pyodbc.connect(conn_str)

(Note that there are no commas between the individual string components.)

---

Related Topics