Connecting MS SQL Server using connectorx in python-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

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