funing
funing
采纳率0%
2018-12-24 10:18 阅读 3.1k

如何用python遍历SQLServer数据库中某个表的所有记录,并修改。

5

想用python遍历某个数据库中的一个表,依次读出每条记录,并修改保存。
请问该如何编写代码?谢谢

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • weixin_43135709 . Red Army '' 2018-12-24 02:42

    declare @row varchar(30)
    declare @tbname varchar(30)
    DECLARE string_find_cursor CURSOR FAST_FORWARD FOR

    SELECT [name]

    FROM sysobjects where type='U' and name like 'A%'
    ORDER BY [name]

    OPEN string_find_cursor

    FETCH NEXT FROM string_find_cursor

    INTO @tbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --select @row=count(*) from @tbname
    select @row=rows from sysindexes where id = object_id(cast(@tbname as varchar(30))) and indid in (0,1)
    if(cast(@row as int))>0
    print @tbname+' '+@row+' '+'select * from '+@tbname
    FETCH NEXT FROM string_find_cursor

    INTO @tbname

    END

    CLOSE string_find_cursor

    DEALLOCATE string_find_cursor

    参考 : https://blog.csdn.net/my98800/article/details/76602277

    你可以看看

    点赞 评论 复制链接分享
  • Smartree Smartree 2018-12-24 11:08

    应该要表明Python版本MSSql版本,不过可以先参考代码:

    from os import getenv
    import pymssql
    
    server = getenv("PYMSSQL_TEST_SERVER")
    user = getenv("PYMSSQL_TEST_USERNAME")
    password = getenv("PYMSSQL_TEST_PASSWORD")
    
    conn = pymssql.connect(server, user, password, "tempdb")
    cursor = conn.cursor()
    cursor.execute("""
    IF OBJECT_ID('persons', 'U') IS NOT NULL
        DROP TABLE persons
    CREATE TABLE persons (
        id INT NOT NULL,
        name VARCHAR(100),
        salesrep VARCHAR(100),
        PRIMARY KEY(id)
    )
    """)
    cursor.executemany(
        "INSERT INTO persons VALUES (%d, %s, %s)",
        [(1, 'John Smith', 'John Doe'),
         (2, 'Jane Doe', 'Joe Dog'),
         (3, 'Mike T.', 'Sarah H.')])
    # you must call commit() to persist your data if you don't set autocommit to True
    conn.commit()
    
    cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
    for row in cursor:
        print("ID=%d, Name=%s" % (row['id'], row['name']))
    
    conn.close()
    
    点赞 评论 复制链接分享

相关推荐