CODEWITHSUNDEEP
pythonexcelpandasconnectionpyodbc
LLONDON
LLONDON

Reputation: 11

Convert OLE DB SQL connection string into a dataframe?

I am very new to python and SQL. I have a DB query in Excel which is very large that I am trying to move to Python directly. Currently i read it in from excel using read_excel().

Ive installed import pyodbc and tried to take the connection string from excel and put it into python.

default code that i have written is:

import pyodbc
conn_str = 'Provider.....'
conn = pyodbc.connect(conn_str)

SQL_Query = pd.read_sql_query(
                '''SELECT  *        
                FROM [MarketData].[Gas].[KplerVesselHistory]''', conn)

The excel connection info is as follows:

Connection type: OLE DB Query

Connection string: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=MarketData;Data Source=FOTDAL02PR;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=[blank];Use Encryption for Data=False;Tag with column collation when possible=False

Command type: SQL

The error I'm getting is:

Traceback (most recent call last):
  File "C:\dev\bin\pycharm\helpers\pydev\_pydevd_bundle\pydevd_exec2.py", line 3, in Exec
    exec(exp, global_vars, local_vars)
  File "<string>", line 3, in <module>
pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Upvotes: 0

Views: 4926

Answers (1)

Richard Crossley
Richard Crossley

Reputation: 586

Something like this might work:

This connects using the ODBC driver for SQL Server, runs the query and returns it to Pandas DataFrame and then prints the output.

import pyodbc
import pandas


driver = '{ODBC Driver 17 for SQL Server}'
server = 'server_name\instance_name'
database = 'MarketData'
sql = 'SELECT  * FROM [MarketData].[Gas].[KplerVesselHistory]'

conn_str = 'Driver={};Server={};Trusted_Connection=True;Database={}'.format(driver, server, database)
conn = pyodbc.connect(conn_str)
df = pandas.read_sql(sql, conn)
print(df)

Upvotes: 1

Related Questions