Connecting MS SQL Server using connectorx in python

Question

I am facing issues while trying to connect with MSSQL Server using connectorx package in python. I have already verified all the connection details through MS SQL Server Management Studio. I have installed version connectorx version 0.2.3

import urllib.parse
import connectorx as cx
mssql_url = f"mssql://{urllib.parse.quote_plus('User ID')}:{urllib.parse.quote_plus('Password')}@Server URL:1433/Database"
query = "SELECT * FROM table"
df = cx.read_sql(mssql_url, query)

Output of the script: [2022-01-27T12:02:13Z ERROR tiberius::tds::stream::token] message=Login failed for user 'User ID'. code=18456 [2022-01-27T12:02:14Z ERROR tiberius::tds::stream::token] message=Login failed for user 'User ID'. code=18456 [2022-01-27T12:02:14Z ERROR tiberius::tds::stream::token] message=Login failed for user 'User ID'. code=18456 [2022-01-27T12:02:16Z ERROR tiberius::tds::stream::token] message=Login failed for user 'User ID'. code=18456 [2022-01-27T12:02:19Z ERROR tiberius::tds::stream::token] message=Login failed for user 'User ID'. code=18456 [2022-01-27T12:02:26Z ERROR tiberius::tds::stream::token] message=Login failed for user 'User ID'. code=18456 [2022-01-27T12:02:38Z ERROR tiberius::tds::stream::token] message=Login failed for user 'User ID'. code=18456 Traceback (most recent call last): File "", line 1, in File "/app/path/xxxxxxxx/dev/lib/python3.8/site-packages/connectorx/init.py", line 118, in read_sql result = _read_sql( RuntimeError: Timed out in bb8

Try by replacing 'User ID' with your actual user that you used in Studio. — Rob Py, Jan 27 '22 at 14:13
I used the same user id I used in Studio. "User ID" or "Password" is just indicative placeholder for actual values. — kanishka kartikeya, Jan 27 '22 at 14:15

score 0 · Answer 1 · answered Jan 27 '22 at 14:10

I am using mysql.connector, it works well for me:

import pandas as pd
import mysql
import mysql.connector

host = "1.1.1.1:1234"
user = "myusername"; 
password = "mypassword"
database = "mydb"


def load_db():
connection = mysql.connector.connect(host=host, user=user, password=password, database=database)
cursor = connection.cursor()
cursor.execute("SELECT * FROM mytable")
field_names = [i[0] for i in mycursor.description]
result = cursor.fetchall()
dataframe = pd.DataFrame(result, columns=field_names)

will this work for mssql as well? – kanishka kartikeya Jan 27 '22 at 14:13 — kanishka kartikeya, Jan 27 '22 at 14:13

score 0 · Answer 2 · answered Jul 26 '23 at 01:37

I had to figure this out recently as well. As of today, it looks like connectorx does not directly support pyodbc. github issue #233 The work around is essentially answered answered in this question

import connectorx 

username = 'my_username'
password = '1234'
server = 'SERVER1'
database = 'db1'
trusted_conn = 'no' # or yes

query = "SELECT * FROM table"

mssql_url = f'mssql://{username}:{password}@{server}/{database}?driver=SQL+Server&trusted_connection={trusted_conn}'

df = connectorx.read_sql(mssql_url, query)

score -1 · Answer 3 · answered Jan 27 '22 at 14:22

I don't think that would work, that's for MySQL, Have you tried this:

I get from here: https://qastack.id/programming/33725862/connecting-to-microsoft-sql-server-using-python

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,))

Connecting MS SQL Server using connectorx in python

3 Answers3