[PyQt]使用QSqlQuery操作Sql Server数据库

测试数据库名称Test
测试表格名称Person
字段Id:类型uniqueidentifier,默认值:newId()
字段Name:类型nvarchar(500)
字段Photo:类型varbinary(MAX)

#导入QtSql模块
from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from PyQt5.QtCore import QIODevice
from PyQt5.QtGui import QImage

#创建数据库连接并打开(未指定数据库名,创建默认连接)
db = QSqlDatabase.addDatabase("QODBC")
db.setDatabaseName("Driver={Sql Server};Server=localhost;Database=TEST;Uid=sa;Pwd=123456")
db.open()

#创建查询对象(使用默认数据库连接)
query = QSqlQuery()

#插入记录并返回新记录Id值
query.exec("INSERT INTO Person(Name) OUTPUT INSERTED.Id VALUES('Tom')")
query.next()
NewId = query.value("Id")

#删除记录,使用"?"操作符绑定值
query.prepare("DELETE FROM Person WHERE Name=?")
query.bindValue(0,"Tom")
query.exec_()                   #此处调用exec_()

#将图片保存到数据库,使用冒号占位符
query.prepare("INSERT INTO Person(Name,Photo) VALUES(:N, :P)")

imagebytes = QByteArray()
buffer = QBuffer(imagebytes)
buffer.open(QIODevice.WriteOnly)
image = QImage(r"d:\test.jpg")
image.save(buffer,"JPG")
buffer.close()

query.bindValue(":N","张三")
query.bindValue(":P",imagebytes)
query.exec_()

#从数据库载入图片
query.prepare("SELECT Photo FROM Person WHERE Name=:Nm")
query.bindValue(":Nm","张三")
query.exec_()

query.next()
imagebytes = query.value("Photo")
buffer = QBuffer(imagebytes)
buffer.open(QIODevice.ReadOnly)
image = QImage()
image.load(buffer,"JPG")
buffer.close()

posted @ 2018-09-13 10:08  乐松  阅读(4511)  评论(0编辑  收藏  举报