pyodbc

pyodbc.connect() works, but not sqlalchemy.create_engine().connect()

A Pass through exact Pyodbc string works for me: import pandas as pd from sqlalchemy import create_engine from sqlalchemy.engine import URL connection_string = ( r”Driver=ODBC Driver 17 for SQL Server;” r”Server=(local)\SQLEXPRESS;” r”Database=myDb;” r”Trusted_Connection=yes;” ) connection_url = URL.create( “mssql+pyodbc”, query={“odbc_connect”: connection_string} ) engine = create_engine(connection_url) df = pd.DataFrame([(1, “foo”)], columns=[“id”, “txt”]) pd.to_sql(“test_table”, engine, if_exists=”replace”, index=False)

how to catch specific pyodbc error message

This worked for me. try: cnxn = pyodbc.connect(…) except pyodbc.Error as ex: sqlstate = ex.args[0] if sqlstate == ‘28000’: print(“LDAP Connection failed: check password”) There are different SQLSTATES and you can have if-else statements to print out the cause. Similarly, try: cnxn = pyodbc.connect(…) except pyodbc.Error as ex: sqlstate = ex.args[1] print(sqlstate) will give you … Read more

sql.h not found when installing PyODBC on Heroku

To follow up on the answer below… Example for Ubuntu: sudo apt-get install unixodbc unixodbc-dev Example for CentOS: sudo yum install unixODBC-devel Example for Fedora: sudo dnf install unixODBC-devel On Windows: conn = pyodbc.connect(‘DRIVER={SQL Server};SERVER=yourserver.yourcompany.com;DATABASE=yourdb;UID=user;PWD=password’) On Linux: conn = pyodbc.connect(‘DRIVER={FreeTDS};SERVER=yourserver.yourcompany.com;PORT=1433;DATABASE=yourdb;UID=user;PWD=password;TDS_VERSION=7.2’)

Connecting to SQL Server 2012 using sqlalchemy and pyodbc

The file-based DSN string is being interpreted by SQLAlchemy as server name = c, database name = users. I prefer connecting without using DSNs, it’s one less configuration task to deal with during code migrations. This syntax works using Windows Authentication: engine = sa.create_engine(‘mssql+pyodbc://server/database’) Or with SQL Authentication: engine = sa.create_engine(‘mssql+pyodbc://user:password@server/database’) SQLAlchemy has a thorough … Read more

Remote connection to MS SQL – Error using pyodbc vs success using SQL Server Management Studio

Try specifying the port: import pyodbc server = r”xxxER\xxxSQLSERV” db = “xxxDB” user = “xxx” password = “xxxx” port = “1433” conn = pyodbc.connect(‘DRIVER={SQL Server};SERVER=’ + server + ‘;PORT=’ + port + ‘;DATABASE=’ + db +’;UID=’ + user + ‘;PWD=’ + password) If you’re still having issues, try using the IP or FQDN of the … Read more

Python – pyodbc call stored procedure with parameter name

I tested this using the following stored procedure in SQL Server 2008 R2: CREATE PROCEDURE [dbo].[breakfast] @person varchar(50) = ‘nobody’, @food varchar(50) = ‘tofu’ AS BEGIN SET NOCOUNT ON; SELECT @person + ‘ likes to eat ‘ + @food END The Bad News (“CALL”) I found that sql = “””\ { CALL breakfast (@food=?, @person=?) … Read more

pyodbc the sql contains 0 parameter markers but 1 parameters were supplied’ ‘hy000’

Parameters in an SQL statement via ODBC are positional, and marked by a ?. Thus: # SQL with parameters start res = cursor.execute(”’ SELECT * FROM TABLE WHERE TABLE.TIMESTAMP BETWEEN ? AND ? ”’, STARTDATE, ENDDATE) # SQL with parameters stop Plus, it’s better to avoid passing dates as strings. Let pyodbc take care of … Read more