So i have a stored procedure that i want to connect to and execute in python so i can export the data as a fancy table.
Here is the code im using, with the comments being code that i have tried.
import plotly as py
import plotly.figure_factory as ff
import pandas as pd
import pypyodbc
import pyodbc
import datetime
import seaborn as sns
import matplotlib.pyplot as plt
#force the ODBC driver to use case-sensitive column names
pypyodbc.lowercase = True
startdate = '2018-10-23 08:00'
enddate = '2018-10-24 12:00'
params = startdate,enddate
##Establishing a connection to SQL database
connection = pypyodbc.connect('Driver={SQL Server};'
'Server=LOLOL;'
'Database=LOL_Collection;'
'trusted_connection=yes;')
crsr = connection.cursor()
SQL = """
SET NOCOUNT ON
declare @start = '2018-10-23 08:00'
declare @end = '2018-10-24 12:00'
exec [dbo].[AM_SIGNAL_Histogram] @start, @end
"""
#SQL = """
#
#exec [dbo].[AM_SIGNAL_Histogram] @start, @end
#
#"""
#result = crsr.execute(SQL)
#
#print(result.fetchall())
df = pd.read_sql(SQL,connection)
connection.close()
And this is the error that i keep on getting:
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.")
Thanks for the help, Kenneth
The problem is not with your Python code, it's with your T-SQL. Running it directly in SQL Server Management Studio (SSMS),
SET NOCOUNT ON
declare @start = '2018-10-23 08:00'
declare @end = '2018-10-24 12:00'
exec [dbo].[AM_SIGNAL_Histogram] @start, @end
produces
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@start".
Your declare statements are missing the data type. The following modification works for me:
SET NOCOUNT ON;
declare @start varchar(50) = '2018-10-23 08:00';
declare @end varchar(50) = '2018-10-24 12:00';
exec [dbo].[AM_SIGNAL_Histogram] @start, @end;