gpt4 book ai didi

python - pypyodbc - 使用存储过程并将数据存储到数据框中

转载 作者:太空宇宙 更新时间:2023-11-03 21:39:40 25 4
gpt4 key购买 nike

所以我有一个存储过程,我想连接到它并在 python 中执行,这样我就可以将数据导出为一个精美的表。

这是我使用的代码,注释是我尝试过的代码。

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()

这是我不断收到的错误:

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.")

感谢您的帮助,肯尼思

最佳答案

问题不在于您的 Python 代码,而在于您的 T-SQL。直接在 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

产生

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".

您的declare语句缺少数据类型。以下修改对我有用:

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;

关于python - pypyodbc - 使用存储过程并将数据存储到数据框中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52973196/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com