How to pass connection parameter "ApplicationIntent=ReadOnly" when using pymssql

Question

I only have readonly access to a SQL Server db. How do I pass connection parameter "ApplicationIntent=ReadOnly" when using pymssql to connect to the database? If pymssql doesn't support it, is there any other python library that I can use?

score 3 · Answer 1 · answered Oct 11 '18 at 08:27

3

The only way to configure readonly connection intent with pymssql seems to be to configure freetds on which it's based, but it did not work out for me. I've had to use pyodbc instead of pymssql

from pyodbc import connect
conn = connect(
    'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + host + ';PORT=' + port + ';DATABASE='
    + database + ';UID=' + user + ';PWD=' + password + ';ApplicationIntent=ReadOnly')

answered Oct 11 '18 at 08:27

Jack

645
7
14

Thanks for the solution, this is working fine! The only requirement is that the ODBC driver for MSSQL is installed on the system. For example on my Windows 10 machine I installed [this one](https://www.microsoft.com/en-us/download/details.aspx?id=56567). – costigator Jan 05 '21 at 17:17

score 1 · Accepted Answer · answered Jun 29 '16 at 17:55

1

Because pymssql is dependent on FreeTDS You really need to address the connection parameters to your MS-SQL server used by FreeTDS

Because ApplicationIntent is listed in the freetds user guide as an option you should be able to use it.

answered Jun 29 '16 at 17:55

Steve

34
1

2

The link is not working anymore. Here is the [new one](http://www.freetds.org/userguide/OdbcConnAttr.html). – costigator Jan 05 '21 at 17:14
I came to this thread trying to do same as OP and the answer is little confusing for me. Are we saying we CAN set `ApplicationIntent='ReadOnly'` using `pymssql`? If yes then how? I tried using `conn=pymssql.connect('db_host_nm','db_usr_nm','db_pwd','db_nm',charset='UTF-8',port=1433,ApplicationIntent='ReadOnly')` but keep getting error `TypeError: connect() got an unexpected keyword argument 'ApplicationIntent'` Am I missing something? – 300 Apr 04 '23 at 23:33

score 0 · Answer 3 · answered Jul 21 '16 at 17:31

0

pyodbc has a readonly parameter. For example:

import pyodbc
conn = pyodbc.connect(driver='{SQL Server}', host=Server, database=Database,
                  trusted_connection='yes', user='', password='', readonly = True)

answered Jul 21 '16 at 17:31

Hobbes

199
1
6

Any reason why this was marked as not useful? Works fine for me without any issues? – Neil S Jun 20 '19 at 20:10
Probably because the question is asking about pymssql, not pyodbc. – Nathan Griffiths Nov 03 '20 at 19:22

How to pass connection parameter "ApplicationIntent=ReadOnly" when using pymssql

3 Answers3

Linked