Connecting to SQL server using Pyodbc & SQLAlchemy

Question

Here is some background on what I am trying to accomplish: My work created an application that stores information in a SQL database for each job we work on. I am trying to automate some of our workflow process using python, but I would like to have access to the information in the database.

From what I understand, to accomplish this I need to connect the database to a SQL server. I am trying to do this using SQLAlchemy and pyodbc. As part of the application I have Microsoft SQL Server 2008 R2 and SQL Server Native Client 10.0 as the driver.

The issue is I am not able to connect to the SQL server using SQLAlchemy or pyodbc using the create_engine() or connect() methods. I have tried a couple different methods below:

1) Using a DSN: I was unable to create a system or user DSN as I would get this error message Error

2) using SQLAlchemy and a hostname connection:

engine = sqlalchemy.create_engine("mssql+pyodbc://user:password@.\DT_SQLEXPR2008/C:\SQLTest\JobDB.mdf?driver=SQL+Server+Native+Client+10.0")
engine.connect()

3) using SQLAlchemy and windows authentication:

engine = sqlalchemy.create_engine('mssql+pyodbc://DT_SQLEXPR2008/C:\SQLTest\JobDB.mdf?driver=SQL+Server+Native+Client+10.0')
engine.connect()

4) Using pyodbc connect() method:

conn = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=.\DT_SQLEXPR2008;DATABASE=C:\SQLTest\JobDB.mdf;UID=user;PWD=password')

In the above I entered my windows username and password in place of "user" and "password". Here is a picture of SQL config. manager showing the existing SQL server and my user I am logged on with.

I have tried setting the server as .\DT_SQLEXPR2008 as well as 'computername'\DT_SQLEXPR2008

Each time I receive this error message:

InterfaceError: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'z003vrzk'. (18456) (SQLDriverConnect); [28000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'z003vrzk'. (18456)") (Background on this error at: http://sqlalche.me/e/rvf5)

Can anyone tell me how to add a database to a SQL server and read the information it contains?

In all the cases the server WAS REACHED. This means that there is no problem with your connection string. The problem is with your LOGIN. **You can find the cause in SQL Server error log. **It maybe wrong password, or server is in Windows only mode and you pass in SQL Server login — sepupic, Dec 13 '18 at 07:20

score 0 · Accepted Answer · answered Dec 19 '18 at 04:16

This will be redundant for SQL users, but I have not seen this question answered using python+pyodbc.

First I needed to connect to the master database residing on the SQL server instance on my computer:

connMaster = pyodbc.connect('DRIVER={SQL Server Native Client 10.0}; 
SERVER=.\DT_SQLEXPR2008;DATABASE=master;Trusted_Connection=yes;')
cursorMaster = connMaster.cursor()

DT_SQLEXPR2008 is the SQL server instance name, master is the database name, and I'm connecting using windows authentication.

Next I need to attach the database residing in the path: C:\Path\To\SQL\JobDB.mdf:

    sql1 = "CREATE DATABASE PBJobDB"
    sql2 = "ON (Filename = '{pathMDF}'), (Filename = '{pathLDF}')".format(pathMDF = pathMDF, pathLDF = pathLDF)
    sql3 = "FOR Attach"
    sql = sql1 + " " + sql2 + " " + sql3
    print(sql)
    connMaster.autocommit = True
    cursorMaster.execute(sql)
    connMaster.autocommit = False

PathMDF and PathLDF are file path names to the master data file and log file.

If you want to connect to a database in a networked location, we will need TRACEON 1807 (haven't gotten this working yet).

That depends on what you mean. I think SQLAlchemy has a utility to get the underlying cursor and connection objects, but its more of an ORM tool. If your goal is to create new tables and relationships then SQLAlchemy is a great tool. The [documentation](https://www.sqlalchemy.org/features.html) notes that it has DDL statements and can uncover the structure of an existing database. But, if you only want to attach existing databases then it depends on which DBMS you're using — J.Vo, Feb 06 '20 at 14:16

Connecting to SQL server using Pyodbc & SQLAlchemy

1 Answers1