PYTHON连接SQLSERVER实现增删改查等基本操作

python7年前 (2019)发布 admin

406 0 0

转自 https://blog.csdn.net/stanwuc/article/details/82182801

import pymssql 
import config
server = server1
user = user1
password = password1
conn = pymssql.connect(server, user, password, database1)  #获取连接
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.')])
conn.commit()
 
 
####删除数据
cursor.execute(
    "delete from persons where id=2"
    )
conn.commit()
 
 
# 查询数据并遍历，结果默认存放到元组之中，要存到字典，需修改cursor = conn.cursor(as_dict=True)
cursor.execute('SELECT * FROM persons WHERE id={0}'.format(3))
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()
 
 
####更改数据
cursor.execute(
    "update persons set name ='haha' where id=1"
    )
conn.commit()
 
 
####创建存储过程
cursor.execute("""
        CREATE PROCEDURE FindPerson
            @name VARCHAR(100)
        AS BEGIN
            SELECT * FROM persons WHERE name = @name
        END
        """)
 
 
#####调用存储过程
cursor.callproc('FindPerson', ('haha',))
for row in cursor:
    print(row)
 
 
####执行含聚合函数的语句
cursor.execute('SELECT COUNT(*) FROM persons')
for row in cursor:
    print(row[0])
 
 
conn.close()#手动关闭连接
 
 
#####用with实现自动关闭conn
with pymssql.connect("", "", "", "") as conn:
    with conn.cursor(as_dict=True) as cursor:   # 数据存放到字典中
        cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
        for row in cursor:
            print(row)

个人测试代码

import pymssql

conn=pymssql.connect(host='127.0.0.1',user='sa',password='abc.123',database='hy_kqzx')
cur=conn.cursor()
cur.execute("select cardcode, accountsCode from [9002].tbCard where cardcode='0000000001'")
print (cur.fetchall())
cur.close()
conn.close()

事务处理

import pymssql

conn=pymssql.connect(host='127.0.0.1',user='sa',password='abc.123',database='hy_kqzx')
cur=conn.cursor()
# SQL 插入语句
sql = "update [9002].tbcard set AccountsCode='55750596' where cardcode='0000000001'"
try:
   # 执行sql语句
   cur.execute(sql)
   # 提交到数据库执行
   conn.commit()
   print('数据提交')
except:
   # Rollback in case there is any error
   conn.rollback()
   print('数据回滚')
cur.execute("select cardcode, AccountsCode from [9002].tbCard where cardcode='0000000001'")
print (cur.fetchall())
cur.close()
conn.close()

# python