有没有办法使用pyodbc向sql server 2005插入请求scope_identity()

发布于 2024-10-09 07:47:08 字数 388 浏览 2 评论 0原文

我有这个很棒的 pyodbc 库。我尝试下面的代码，它应该插入一行并返回行 ID，但它不起作用。顺便说一句，我在服务器和客户端上使用 sql server 2005，Windows 操作系统

...
con = pyodbc.connect('conectionString', autocommit = True)
cur = con.execute(
                  "insert into sometable values('something');
                  select scope_identity() as id"
                  )
for id in cur:
   print id
...

有什么想法吗？

原文

I have this great pyodbc lib. I try the code below, it supposed to insert a row and return the row id but it didn't work. by the way I'm using sql server 2005 on server and client is windows os

...
con = pyodbc.connect('conectionString', autocommit = True)
cur = con.execute(
                  "insert into sometable values('something');
                  select scope_identity() as id"
                  )
for id in cur:
   print id
...

some idea?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

音盲 2024-10-16 07:47:08

尝试使用 OUTPUT 子句的一条语句

cur = con.execute(
         "insert into sometable OUTPUT INSERTED.idcolumn values('something')"
         )
row = cur.fetchone()
lastrowid = row[0]

编辑：这应该可以解决 Joe S 评论的问题。

Try this, one statement with the OUTPUT clause

cur = con.execute(
         "insert into sometable OUTPUT INSERTED.idcolumn values('something')"
         )
row = cur.fetchone()
lastrowid = row[0]

Edit: This should get around the issue commented by Joe S.

回复收藏 0 原文

浅沫记忆 2024-10-16 07:47:08

使用 SCOPE_IDENTITY() 是一种可行的方法，因为由于触发器的原因，使用 OUTPUT 和 @@IDENTITY 存在限制和怪癖。

使用您的代码片段，您只需添加对 nextset 的调用即可获取身份证号。

...
con = pyodbc.connect('conectionString', autocommit = True)
cur = con.execute(
                  "insert into sometable values('something');
                  select scope_identity() as id"
                  )
cur.nextset()
for id in cur:
   print id
...

Using SCOPE_IDENTITY() is the way to go as there are limitations and quirks using OUTPUT and @@IDENTITY because of triggers.

Using your code snipped, you just need to add a call to nextset to get the id.

...
con = pyodbc.connect('conectionString', autocommit = True)
cur = con.execute(
                  "insert into sometable values('something');
                  select scope_identity() as id"
                  )
cur.nextset()
for id in cur:
   print id
...

回复收藏 0 原文

~没有更多了~