Python使用pyodbc访问数据库操作方法详解
本文实例讲述了Python使用pyodbc访问数据库操作方法。
数据库连接
数据库连接网上大致有两种方法,一种是使用pyodbc,另一种是使用win32com.client,测试了很多遍,最终只有pyodbc成功,而且比较好用,所以这里只介绍这种方法
工具库安装
在此基础上安装pyodbc工具库,在cmd窗口执行如下语句安装
pipinstallpyodbc
如果安装了anaconda也可以使用condainstallpyodbc
分享给大家供大家参考,具体如下:
检验是否可以正常连接数据库检查是否有一个MicrosoftAccessODBC驱动程序可用于你的Python环境(在Windows上)的方法:
>>>importpyodbc
>>>[xforxinpyodbc.drivers()ifx.startswith('MicrosoftAccessDriver')]
如果你看到一个空列表,那么您正在运行64位Python,并且需要安装64位版本的“ACE”驱动程序。如果您只看到['MicrosoftAccessDriver(*.mdb)']并且需要使用.accdb文件,那么您需要安装32位版本的“ACE”驱动程序
安装64位的ODBC驱动器:
64位ODBC驱动器的下载地址https://www.microsoft.com/en-us/download/details.aspx?id=13255
直接安装会报错,所以我们需要修改一下文件AccessDatabaseEngine_X64.exe,先对其进行解压,然后打开AccessDatabaseEngine_X64文件夹,有一个AceRedist.msi文件。用Orca软件将AceRedist.msi打开,找到找到LaunchCondition里面的BLOCKINSTALLATION,删除那一行数据并进行保存。然后再运行AceRedist.msi,就可以把64位的ODBC驱动器安装成功。
如果感觉上面的操作比较麻烦,可以直接下载毛票票小编已经处理过的版本。
下载地址:https://www.nhooo.com/softs/695978.html
注意:
1、不用配置数据源
2、Orcad的下载地址https://www.nhooo.com/softs/16217.html
下面是经过毛票票小编测试过的代码
access是2000的,理论上2010也可以。
importpyodbc DBfile=r"F:\python\caiji.mdb"#数据库文件需要带路径 print(DBfile) conn=pyodbc.connect(r"DRIVER={MicrosoftAccessDriver(*.mdb,*.accdb)};DBQ="+DBfile+";Uid=;Pwd=;") cursor=conn.cursor() SQL="SELECT*fromsites;" forrowincursor.execute(SQL): print(row) cursor.close() conn.close()
完整测试代码
#-*-coding:utf-8-*- importpyodbc #连接数据库(不需要配置数据源),connect()函数创建并返回一个Connection对象 cnxn=pyodbc.connect(r'DRIVER={MicrosoftAccessDriver(*.mdb,*.accdb)};DBQ=.\data\goods.mdb') #cursor()使用该连接创建(并返回)一个游标或类游标的对象 crsr=cnxn.cursor() #打印数据库goods.mdb中的所有表的表名 print('``````````````goods``````````````') fortable_infoincrsr.tables(tableType='TABLE'): print(table_info.table_name) l=crsr.execute("SELECT*fromgoodsWHEREgoodsId='0001'")#[('0001','扇叶',20,'A公司','B公司',2000,2009)] rows=crsr.execute("SELECTcurrentStockfromgoods")#返回的是一个元组 foriteminrows: print(item) l=crsr.execute("UPDATEusersSETusername='lind'WHEREpassword='123456'") print(crsr.rowcount)#想知道数据修改和删除时,到底影响了多少条记录,这个时候你可以使用cursor.rowcount的返回值。 #修改数据库中int类型的值 value=10 SQL="UPDATEgoods"\ "SETlowestStock="+str(value)+""\ "WHEREgoodsId='0005'" #删除表users crsr.execute("DROPTABLEusers") #创建新表users crsr.execute('CREATETABLEusers(loginVARCHAR(8),useridINT,projidINT)') #给表中插入新数据 crsr.execute("INSERTINTOusersVALUES('Linda',211,151)") '''''' #更新数据 crsr.execute("UPDATEusersSETprojid=1WHEREuserid=211") #删除行数据 crsr.execute("DELETEFROMgoodsWHEREgoodNum='0001'") #打印查询的结果 forrowincrsr.execute("SELECT*fromusers"): print(row) #提交数据(只有提交之后,所有的操作才会对实际的物理表格产生影响) crsr.commit() crsr.close() cnxn.close()
1、连接数据库
1)直接连接数据库和创建一个游标(cursor)
cnxn=pyodbc.connect('DRIVER={SQLServer};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass') cursor=cnxn.cursor()
2)使用DSN连接。通常DSN连接并不需要密码,还是需要提供一个PSW的关键字。
cnxn=pyodbc.connect('DSN=test;PWD=password') cursor=cnxn.cursor()
关于连接函数还有更多的选项,可以在pyodbc文档中的connectfuntion和ConnectionStrings查看更多的细节
2、数据查询(SQL语句为select...from..where)
1)所有的SQL语句都用cursor.execute函数运行。如果语句返回行,比如一个查询语句返回的行,你可以通过游标的fetch函数来获取数据,这些函数有(fetchone,fetchall,fetchmany).如果返回空行,fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
cursor.execute("selectuser_id,user_namefromusers") row=cursor.fetchone() ifrow: printrow
2)Row这个类,类似于一个元组,但是他们也可以通过字段名进行访问。
cursor.execute("selectuser_id,user_namefromusers") row=cursor.fetchone() print'name:',row[1]#accessbycolumnindex print'name:',row.user_name#oraccessbyname
3)如果所有的行都被检索完,那么fetchone将返回None.
while1: row=cursor.fetchone() ifnotrow: break print'id:',row.user_id
4)使用fetchall函数时,将返回所有剩下的行,如果是空行,那么将返回一个空列。(如果有很多行,这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中,然后由数据库服务器分批发送。一次只读取你需要的行,将会大大节省内存空间)
cursor.execute("selectuser_id,user_namefromusers") rows=cursor.fetchall() forrowinrows: printrow.user_id,row.user_name
5)如果你打算一次读完所有数据,那么你可以使用cursor本身。
cursor.execute("selectuser_id,user_namefromusers"): forrowincursor: printrow.user_id,row.user_name
6)由于cursor.execute返回一个cursor,所以你可以把上面的语句简化成:
forrowincursor.execute("selectuser_id,user_namefromusers"): printrow.user_id,row.user_name
7)有很多SQL语句用单行来写并不是很方便,所以你也可以使用三引号的字符串来写:
cursor.execute(""" selectuser_id,user_name fromusers wherelast_logon<'2001-01-01' andbill_overdue='y' """)
3、参数
1)ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值,用来传递给SQL语句中的问号。
cursor.execute(""" selectuser_id,user_name fromusers wherelast_logon andbill_overdue=? """,'2001-01-01','y')
这样做比直接把值写在SQL语句中更加安全,这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句,这样做也更加效率。
3)pythonDBAPI明确说明多参数时可以使用一个序列来传递。pyodbc同样支持:
cursor.execute(""" selectuser_id,user_name fromusers wherelast_logon andbill_overdue=? """,['2001-01-01','y'])
cursor.execute("selectcount(*)asuser_countfromuserswhereage>?",21) row=cursor.fetchone() print'%dusers'%row.user_count
4、数据插入
1)数据插入,把SQL插入语句传递给cursor的execute函数,可以伴随任何需要的参数。
cursor.execute("insertintoproducts(id,name)values('pyodbc','awesomelibrary')") cnxn.commit()
cursor.execute("insertintoproducts(id,name)values(?,?)",'pyodbc','awesomelibrary') cnxn.commit()
注意调用cnxn.commit()函数:你必须调用commit函数,否者你对数据库的所有操作将会失效!当断开连接时,所有悬挂的修改将会被重置。这很容易导致出错,所以你必须记得调用commit函数。
5、数据修改和删除
1)数据修改和删除也是跟上面的操作一样,把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时,到底影响了多少条记录,这个时候你可以使用cursor.rowcount的返回值。
cursor.execute("deletefromproductswhereid<>?",'pyodbc') printcursor.rowcount,'productsdeleted' cnxn.commit()
2)由于execute函数总是返回cursor,所以有时候你也可以看到像这样的语句:(注意rowcount放在最后面)
deleted=cursor.execute("deletefromproductswhereid<>'pyodbc'").rowcount cnxn.commit()
同样要注意调用cnxn.commit()函数
6、小窍门
1)由于使用单引号的SQL语句是有效的,那么双引号也同样是有效的:
deleted=cursor.execute("deletefromproductswhereid<>'pyodbc'").rowcount
2)假如你使用的是三引号,那么你也可以这样使用:
deleted=cursor.execute(""" delete fromproducts whereid<>'pyodbc' """).rowcount
3)有些数据库(比如SQLServer)在计数时并没有产生列名,这种情况下,你想访问数据就必须使用下标。当然你也可以使用"as"关键字来取个列名(下面SQL语句的"asname-count")
row=cursor.execute("selectcount(*)asuser_countfromusers").fetchone() print'%susers'%row.user_count
4)假如你只是需要一个值,那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
count=cursor.execute("selectcount(*)fromusers").fetchone()[0] print'%susers'%count
如果列为空,将会导致该语句不能运行。fetchone()函数返回None,而你将会获取一个错误:NoneType不支持下标。如果有一个默认值,你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
maxid=cursor.execute("selectcoalesce(max(id),0)fromusers").fetchone()[0]
在这个例子里面,如果max(id)返回NULL,coalesce(max(id),0)将导致查询的值为0。
更多关于Python相关内容感兴趣的读者可查看本站专题:《Python+MySQL数据库程序设计入门教程》、《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》
希望本文所述对大家Python程序设计有所帮助。