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?
0
Add a comment
|
4 Answers
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')
1 Comment
costigator
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.
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.
2 Comments
costigator
The link is not working anymore. Here is the new one.
300
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?Latest version of pymssql has read_only property enabled.
pymssql.connect("host", "user", "pwd", "database", read_only=True)
Comments
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)
2 Comments
Neil S
Any reason why this was marked as not useful? Works fine for me without any issues?
Nathan Griffiths
Probably because the question is asking about pymssql, not pyodbc.