将日期传递给正在调用sql server的python中的函数 [英] passing a date to a function in python that is calling sql server

问题描述

我正在尝试将date1和date 2传递给python中的函数，该函数从sql server创建数据框

I am trying to pass date1 and date 2 to a function in python that creates a dataframe from a sql server

  import datetime as dt
  import pandas as pd
  import pyodbc
  import sqlalchemy
  from pandas import Series,DataFrame
  cnx=sqlalchemy.create_engine("mssql+pyodbc://Omnius:MainBrain1@172.31.163.135:1433/Basis?driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0")

def func1(date1,date2):
sqlquery = "select top 10000 * from Pretty_Txns where Pay_Date between '"+date1+"' and '"+date2+"'"
df=pd.read_sql(sqlquery,cnx)

当我尝试从ipython调用此函数

When I try and call this function from ipython

  func1(dt.date(2015,05,01),dt.date(2015,05,02))

  ----> 8     sqlquery = "select top 10000 * from Pretty_Txns where Pay_Date between  '"+date1+"' and '"+date2+"'"

 TypeError: cannot concatenate 'str' and 'datetime.date' objects

我应该如何调用该函数，以免出现类型错误.

How should I call the function so that I dont get the type error.

推荐答案

这应该可以帮助您入门.除非日期格式匹配，否则您的SQL可能无法正常工作.打印出sqlquery并在CLI中进行测试.

This should get you started. Your SQL will likely not work unless the date formats match. Print out the sqlquery and test in CLI.

转换示例:

t = datetime.datetime(2012, 2, 23, 0, 0)
t.strftime('%m/%d/%Y')


def func1(date1,date2):
      strDate1 =  date1.strftime('%m/%d/%Y')
      strDate2 =  date2.strftime('%m/%d/%Y')

      sql = "select top 10000 * from Pretty_Txns where Pay_Date > '%s' and Pay_Date < '%s'  " %    (strDate1, strDate2)
      return sql

sqlquery = func1( DateOne,DateTwo )
#print sqlquery. 

df=pd.read_sql(sqlquery,cnx)

这篇关于将日期传递给正在调用sql server的python中的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！