python 讀寫sql2008 類

2022-09-23 05:16:57 字數 3020 閱讀 9991

import

pymssql

class

mssql:

def__init__

(self,host,user,pwd,db):

self.host =host

self.user =user

self.pwd =pwd

self.db =db

def__getconnect

(self):

ifnot

self.db:

raise(nameerror,"

沒有設定資料庫資訊")

try:

self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="

utf8")

#self.conn = pymssql.connect(self.host,self.user,self.pwd,self.db)

cur =self.conn.cursor()

except

exception as e:

print

(e)

ifnot

cur:

raise(nameerror,"

連線資料庫失敗")

else

:

return

cur

#執行查詢語句

defexecquery(self,sql):

cur = self.__getconnect

() cur.execute(sql)

reslist =cur.fetchall()

#查詢完畢後必須關閉連線

self.conn.close()

return

reslist

#執行非查詢語句

defexecnonquery(self,sql):

cur = self.__getconnect

() cur.execute(sql)

self.conn.commit()

self.conn.close()

#進行資料插入

definsert(self, tablename, params):

"""建立資料庫表

args:

tablename :表名字

key :屬性鍵

value :屬性值

"""key =

value =

for tmpkey, tmpvalue in

params.items():

ifisinstance(tmpvalue, str):"\'

" + tmpvalue + "\'"

)

else

:

try:

attrs_sql = '

(' + '

,'.join(key) + ')'

values_sql = '

values(

' + '

,'.join(value) + ')'

except

exception as e:

none

#print(e)

#print("****************************")

sql = '

insert into %s

'%tablename

sql = sql + attrs_sql +values_sql

print('

_insert:

'+sql)

try:

self.execnonquery(sql)

except

exception as e:

none

#print(e)

if__name__ == "

__main__":

ms = mssql(host="

my-pc\sqlexpress

",user="

sa",pwd="

*********

",db="

sqltest")

#ms = mssql("127.0.0.1","sa","*********","sqltest")

reslist = ms.execquery("

select * from student")

for i in

reslist:

print

(i) reslist = ms.execquery("

select name from student")

for i in

reslist:

print

(i) newsql="

update student set name='%s' where id=1

"%u'蘇銘'

print

(newsql)

ms.execnonquery(newsql.encode(

'utf-8'))

newsql="

delete from student where name='python測試2'

"print

(newsql)

ms.execnonquery(newsql.encode(

'utf-8'))

newsql="

insert into student (name) values ('python測試2')

"print

(newsql)

ms.execnonquery(newsql.encode(

'utf-8'))

ms.insert(

'student

',)#

插入資料(指明表名稱,欄位名,欄位內容對應的字典形式輸入

SQL2008 T SQL Note

1 inner join select a b agaserviceport from mc entity a inner join aga...

SQL2008遊標

最近讓寫一個自動生成資料的儲存過程,其中會遍歷表中資料並做出相應處理,因為資料量不算太大所以使用到了遊標,初識遊標遇到幾個小問題,所以來和大...

SQL2008寫作業

sql2008如何建立定時作業?此方法也適應於sql server2005資料庫,有興趣的可以來看下! 1 開啟 sql server ma...