我想使用python通过sql数据库表获取大量的一天数据,但这需要很长时间。因此,我决定提取部分数据,执行一些操作,并作为结果附加整个数据。为此,我采用了每小时一次的数据,这样服务器就不会占用更长的时间。我把一个列表作为l=[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24]这些项目,即1到24是我循环遍历的小时数,并传递给sql query,以得到一部分一部分的结果
以下是参考代码
(我的代码):
l = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24]
for i in l:
quoted = urllib.parse.quote_plus('DRIVER={SQL Server};'
'SERVER=servername;'
'DATABASE=DB;'
'uid=userid;'
'pwd=****;')
engine =
create_engine('mssql+pyodbc:///odbc_connect{}'.format(quoted))
sql = "select DISTINCT a.companyid, c.RegistrationNo, c.gpsdatetime, c.GPSOdoMeter,
c.GroundSpeed, c.TxnReason from Vehicle_Master a with(nolock), GPSEventsData c
with(nolock) where a.RegistrationNo = c.RegistrationNo and a.companyid = c.customerid and
a.active = 1 and c.gpsdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0) and DATEPART(HOUR, c.gpsdatetime) = i ORDER BY gpsdatetime"
df1 = pd.read_sql_query(sql, engine)"
df1['ChangeInTime'] = df1.groupby('RegistrationNo')['GPSDateTime'].apply(lambda x: x -
x.shift(1))
df1['GroundSpeed'] = df1['GroundSpeed']*(5/18)
df1['ChangeInTimeInSec'] = df1['ChangeInTime'].dt.total_seconds()
df1['ChangeInSpeed'] = df1.groupby('RegistrationNo')['GroundSpeed'].apply(lambda x: x -
x.shift(1))
df1['Acceleration'] = df1['ChangeInSpeed']/df1['ChangeInTimeInSec']
df1.to_sql(name="Accelerations_Breaking",schema = 'dbo', con = engine,if_exists =
'append',index=False)
下面是一个错误:
ProgrammingError:(pyodbc.programingError)('42S22',“[42S22][Microsoft][ODBC SQL Server Driver][SQL Server]列名'i'无效(207)(SQLExecDirectW)“) [SQL:选择不同的a.companyid、c.RegistrationNo、c.gpsdatetime、c.GPSOdoMeter、c.GroundSpeed、c.TxnReason from Vehicle\u Master a with(nolock),其中a.RegistrationNo=c.RegistrationNo,a.companyid=c.customerid,a.active=1,c.gpsdatetime>;=DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0)和DATEPART(HOUR,c.gpsdatetime)=i按gpsdatetime排序] (此错误的背景信息位于:http://sqlalche.me/e/f405)
我不知道我哪里出错了。
请帮忙
错误为“列名'i'无效”。问题是SQL中的
DATEPART(HOUR, c.gpsdatetime) = i
。i
在Python中有一个变量,但是在SQL字符串中直接引用它。应该使用绑定变量,比如DATEPART(HOUR, c.gpsdatetime) = ?
,将该SQL转换为PreparedStatement,或者Python库所称的任何语句,然后在Python中将绑定变量的值设置为i
相关问题 更多 >
编程相关推荐