代码之家 › 专栏 › 技术社区 › Sean McCarthy

尝试从Python插入新创建的临时SQL Server表时对象名无效

python-3.x sql-server

Sean McCarthy · 技术社区 · 7 年前

我试图在Microsoft SQL Server中创建一个临时表,然后将数据插入其中,然后将数据作为数据帧返回给Python,这是最好的。

这是我的连接,工作正常(密码隐藏)。

EKA = turbodbc.connect(dsn='ekaprd', uid='smccarth', pwd='*****', database="Eka", turbodbc_options = turbodbc.make_options(prefer_unicode=True))

下面是我要运行的SQL字符串:

sql_string = """

create table #retaildeals 
(
     TransactionType varchar(10),
     TCID int,
     TID int,
     ItemID int,
     strategyID int,
     Commodity varchar(10),
     BuyOrSell varchar(10),
     Category varchar(50)
)

insert into #retaildeals
select 
     'Physical',
     ti.TransactingCompanyID,
     ti.TransactionID,
     ti.TransactionItemID,
     th.strategyID,
     tpd.Commodity,
     tpd.BuyOrSell,
     isnull(tci.categoryvalue, '')
from TransactionHeader TH (nolock)   
inner join strategy S (nolock)
       on s.StrategyID = th.StrategyID
inner join transactionitem TI (nolock)
       on th.TransactingCompanyID = ti.TransactingCompanyID
       and th.TransactionID = ti.TransactionID
inner join TransactionPhysicalDetail TPD (nolock)        
       on tpd.TransactingCompanyID = ti.TransactingCompanyID
       and tpd.TransactionID = ti.TransactionID
       and tpd.TransactionItemID = ti.TransactionItemID
inner join TransactionCategory TC (nolock)
       on tc.TransactingCompanyID = th.TransactingCompanyID
       and tc.TransactionID = th.TransactionID
left join TransactionCategoryItem TCI (nolock)
       on tc.CategoryItemID1 = tci.CategoryItemID
where
 th.IsVoid = 0
 and ti.IsVoid = 0
 and th.transactiontypeid in (50, 53)
group by
 ti.TransactingCompanyID,
 ti.TransactionID,
 ti.TransactionItemID,
 th.strategyID,
 tpd.commodity,
 tpd.BuyOrSell,
 isnull(tci.categoryvalue, '')
"""

最后,我尝试执行查询字符串:

cursor = EKA.cursor()
cursor.execute(sql_string)

我得到以下错误:

DatabaseError: ODBC error
state: 42S02
native error code: 208
message: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#retaildeals'.

当我在诸如Aqua Data Studio之类的程序中或在SAS中作为passthrough查询执行它时,上面的SQL代码可以正常工作。为什么用Python完成时不起作用?