4

I'm trying to open a mdf sql database file that I have saved to my desktop. How do you open it as a pandas dataframe? so far all I have is the following:

conn=pyodbc.connect(driver='{SQL Server}', dsn=filepath)

Its giving me the error message

OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver]Neither DSN nor SERVER keyword supplied (0) (SQLDriverConnect)')

I found another question that was similar but it was also unanswered. I have also been unable to find a good tutorial to start using sql databases with python I'm very new to the topic. Let me know if there is any extra information I can give. Thanks in advance.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Matthew
  • 393
  • 2
  • 4
  • 15
  • check this https://www.rebasedata.com/python-read-mdf – Abdulrahman Bres Nov 14 '18 at 20:15
  • I cant install poster on python 3 is there another option? – Matthew Nov 14 '18 at 21:03
  • Would this work? https://stackoverflow.com/questions/51734803/read-mdf-file-int-pandas-dataframe – Evan Nov 14 '18 at 21:44
  • I found that one earlier as well but I don't get the server part. I have a mdf file on my desktop is there no way to just open that file in python. Do I have to create a local server somehow to read the mdf file? – Matthew Nov 15 '18 at 13:12

1 Answers1

4

I have a mdf file on my desktop is there no way to just open that file in python.

Well, yes, you could open it as a binary file but then you'd need to write the code to interpret the contents of the file. In other words, you would need to reverse-engineer the logic that SQL Server uses to write database objects to the .mdf file.

It would probably be easier for you to just install SQL Server Express Edition, attach the .mdf file, and then access the database as usual.

Or, instead of manually attaching the .mdf file to the SQL Server instance you could use code like this:

import pandas as pd
import pyodbc

cnxn_str = (
    r'DRIVER=ODBC Driver 11 for SQL Server;'
    r'SERVER=(local)\SQLEXPRESS;'
    r'Trusted_Connection=yes;'
    r'AttachDbFileName=C:\Users\Gord\Desktop\zzz.mdf;'
)
cnxn = pyodbc.connect(cnxn_str)
df = pd.read_sql("SELECT * FROM Table1", cnxn)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Using this code im now getting this error: InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') – Matthew Nov 15 '18 at 15:41
  • 1
    You may have a different version of the ODBC driver installed. Check the list returned by `pyodbc.drivers()` to see which drivers are available to your Python app. – Gord Thompson Nov 15 '18 at 15:50
  • Thanks that fixed the driver error. In order to run this code do I need to have an instance of sql express running? – Matthew Nov 15 '18 at 16:02
  • Yes, an instance of SQL Server will have to be running in order for your Python app to communicate with it. It could be a normal SQL Server Express instance running as a service, or it could be a [LocalDB instance](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-2016-express-localdb) that "spins up" when you start to use it and shuts down when you're finished. – Gord Thompson Nov 15 '18 at 16:15
  • Sorry but I'm still having some issues. I installed 2014 sql express localdb and ran the sqllocaldb program. Then I tried running the code again in python and I get this error: OperationalError: ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. (-1) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server – Matthew Nov 15 '18 at 21:13
  • When you run `sqllocaldb info` does it list an instance named MSSQLLocalDB? If so, are you trying to connect to `SERVER=(localdb)\MSSQLLocalDB` ...? – Gord Thompson Nov 15 '18 at 21:22
  • @sattva_venu - Yes, I would imagine so, if one was to use a [pass-through connection string](https://docs.sqlalchemy.org/en/13/dialects/mssql.html#pass-through-exact-pyodbc-string) – Gord Thompson Feb 06 '20 at 11:25