How to read table in sql script with pandas?

Question

I have a .sql script.

The script first checks function existence, then create a function if not exist.

It then declares variables.

Finally, it executes a query.

The script can produce correct result when executing in sql server management studio.

I used pandas to read the table:

with open('query.sql', 'r') as f:
    query = f.read()

df = pd.read_sql_query(query, conn_str)

But it returns

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near \'GO\'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]\'CREATE FUNCTION\' must be the first statement in a query batch. (111); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@number". (137); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]A RETURN statement with a return value cannot be used in this context. (178); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near \'GO\'. (102)')

What can I do?

have you checked your connection string

Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin

2019-03-20 06:41:28 +00:00
Commented Mar 20, 2019 at 6:41 — Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin, Commented Mar 20, 2019 at 6:41
yes, it is correct.

Chan
– Chan

2019-03-20 07:06:10 +00:00
Commented Mar 20, 2019 at 7:06 — Chan
– Chan, Commented Mar 20, 2019 at 7:06

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-20 07:11:31Z

1

use pd.read_sql

df = pd.read_sql("SELECT * FROM table_name ",connection_string)

so in your case

df = pd.read_sql(query, conn_str)

answered Mar 20, 2019 at 7:11

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to read table in sql script with pandas?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related