Python程序设计课件 第十章 数据库操作

Python程序设计

第十章数据库操作本章知识点导图

SQLite是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的SQL数据库引擎。SQLite是在世界上最广泛部署的SQL数据库引擎。SQLite源代码不受版权限制。SQLite数据库占用资源非常少，仅需几百K内存即可，处理速度快，可直接使用C或配合C#、PHP、Java等其他语言使用。SQLite诞生于2000年，2015年发布了新版本SQLite3。

在Python中操作数据库时，要先导入数据库对应的驱动，然后通过Connection对象和Cursor对象操作数据。最后要确保打开的Connection对象和Cursor对象都正确地被关闭，否则资源就会泄露。在Python中使用SQLite数据库，需要以下几个关键步骤：导入sqlite3模块包：importsqlite3通过sqlite3的connect()函数连接已经存在的或新创建数据库，获得操作数据库的句柄：conn=sqlite3.connect(数据库名称)利用句柄执行SQL指令:conn.execute(sql指令串)利用句柄把操作结果提交给数据库:mit()最后要关闭数据库的连接:conn.close()10.1创建数据库

对于数据库首先要明白一个逻辑关系，有数据库才有表，一个数据库文件下可以有多张数据表。类似于一个Excel文件下可以有多张数据表Sheet1、Sheet2、Sheet3等。

创建和链接一个已有的数据库方法一样。如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象。

首先导入SQLite以驱动数据库模块，该模块是Python集成的内置类库，提供Python操作sqlite3的相关接口，无需安装。连接到SQlite数据库，数据库文件名为test.db，若文件不存在，则自动创建。In[1]:importsqlite3In[2]:conn=sqlite3.connect('test.db')

为了对数据库中的数据表能够进行操作，需要创建游标对象conn.cursor()，通过返回的对象c，执行相应的SQL语句。In[3]:c=conn.cursor()

有了游标，我们可以对表进行各种操作，创建表、对表进行增删改查等。在执行这些操作时，均通过c.execute()执行sql语句。In[4]:c.execute('''CREATETABLEcompany

...:(IDINTPRIMARYKEYNOTNULL,

...:NAMETEXTNOTNULL,

...:AGEINTNOTNULL,

...:ADDRESSCHAR(50),

...:SALARYREAL);

...:''')Out[4]:<sqlite3.Cursorat0x1db5efe6730>上面语句创建了一个叫company的表，它有一个主键ID，一个NAME，一个AGE，以及ADDRESS和SALARY，如果NAME是不可以重复的，可以设置NAME为varchar(10)UNIQUE）。这里需要提醒的是，只有提交了之后才能生效。我们使用数据库连接对象c来进行提交commit和回滚rollback操作。提交之后需要及时关闭游标和数据库链接。In[5]:mit()

...:c.close()

...:conn.close()10.2插入数据在插入记录（数据）之前需要先连接数据库，并创建游标。In[6]:conn=sqlite3.connect('test.db')

...:c=conn.cursor()插入记录用c.execute()句式，并带上sql插入语句。插入语句句式如下："INSERTINTO***(？,？,？)VALUES(^,^,^)"其中***表示数据表的名称；？表示数据表中要插入的记录的相应字段，每个字段之间用逗号分割；^表示？所对应的值。In[7]:c.execute("INSERTINTOcompany(ID,NAME,AGE,ADDRESS,SALARY)\

...:VALUES(1,'Paul',32,'California',20000.00)")

...:

...:c.execute("INSERTINTOcompany(ID,NAME,AGE,ADDRESS,SALARY)\

...:VALUES(2,'Allen',25,'Texas',15000.00)")

...:

...:c.execute("INSERTINTOcompany(ID,NAME,AGE,ADDRESS,SALARY)\

...:VALUES(3,'Teddy',23,'Norway',20000.00)")

...:

...:c.execute("INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)\

...:VALUES(4,'Mark',25,'Rich-Mond',65000.00)")Out[7]:<sqlite3.Cursorat0x1db6022b9d0>插入记录后要记得提交和关闭链接。In[8]:mit()

...:conn.close()也可以一次性插入多条记录，用executemany()的方法来执行。如：reco=[(5,'Paul0',31,'California0',20600.00),(6,'Allen0',26,'Texas0',15500.00),(7,'Teddy0',28,'Norway0',27000.00),(8,'Mark0',23,'Rich-Mond0',65500.00)]c.executemany("insertintocompany(ID,NAME,AGE,ADDRESS,SALARY)values(?,?,?,?,?)",reco)SQL语句中的参数，使用"?"作为替代符号，并在后面的参数中给出具体值。这里不能用Python的格式化字符串，如"%s"，因为这一用法容易受到SQL注入攻击。10.3查询记录

在查询记录时也需要先链接到数据库，并利用以下句式查询，把查询结果赋值给cursor。cursor=c.execute("SELECT?,?,?from*")此处?表示数据表中要查询的记录相应的字段，每个字段之间用逗号分割；*表示数据表名称。In[1]:importsqlite3

In[2]:conn=sqlite3.connect('test.db')

...:c=conn.cursor()

...:

...:cursor=c.execute("SELECTid,name,address,salaryfromCOMPANY")

...:forrowincursor:

...:print("ID=",row[0])

...:print("NAME=",row[1])

...:print("ADDRESS=",row[2])

...:print("SALARY=",row[3],"\n")

...:

...:conn.close()ID=1NAME=PaulADDRESS=CaliforniaSALARY=20000.0

ID=2NAME=AllenADDRESS=TexasSALARY=15000.0

ID=3NAME=TeddyADDRESS=NorwaySALARY=20000.0

ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000.0查询记录时不需要提交commit操作。

在执行查询语句后，Python将返回一个循环器，包含有查询获得的多个记录。可以循环读取，也可以使用sqlite3提供的fetchone()和fetchall()方法读取记录.In[3]:conn=sqlite3.connect('test.db')

...:c=conn.cursor()

In[4]:cursor=c.execute("SELECTid,name,address,salaryfromCOMPANY")

...:c.fetchone()#调用一次显示一条记录，游标下移Out[4]:(1,'Paul','California',20000.0)

In[5]:c.fetchone()Out[5]:(2,'Allen','Texas',15000.0)

In[6]:c.fetchall()#一次性显示所有的查询记录Out[6]:[(3,'Teddy','Norway',20000.0),(4,'Mark','Rich-Mond',65000.0)]

In[7]:conn.close()在SQLite中查询一个表是否存在的方法：SELECTnameFROMsqlite_masterWHEREtype=‘table’ANDname=‘table_name’;table_name就是传进去要查找的表的名字。10.4修改记录修改记录也称之为更新记录。操作句式如下：c.execute("UPDATE***set?=^whereID=n")此处***为数据表名称；?为要修改的字段；^表示要改为的值；n表示要修改的记录序号。In[1]:importsqlite3

...:conn=sqlite3.connect('test.db')

...:c=conn.cursor()

In[2]:c.execute("UPDATECOMPANYsetSALARY=25000.00whereID=1")

...:mit()

In[3]:cursor=conn.execute("SELECTid,name,address,salaryfromCOMPANY")

In[4]:forrowincursor:

...:print("ID=",row[0])

...:print("NAME=",row[1])

...:print("ADDRESS=",row[2])

...:print("SALARY=",row[3],"\n")ID=1NAME=PaulADDRESS=CaliforniaSALARY=25000.0

ID=2NAME=AllenADDRESS=TexasSALARY=15000.0

ID=3NAME=TeddyADDRESS=NorwaySALARY=20000.0

ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000.0

In[5]:conn.close()10.5删除记录删除记录的句式如下：c.execute("DELETEfrom***whereID=n;")其中***表示数据表的名称；n表示要删除的数据记录序号。In[1]:importsqlite3

...:conn=sqlite3.connect('test.db')

...:c=conn.cursor()

In[2]:c.execute("DELETEfromCOMPANYwhereID=2;")

...:mit()

In[3]:cursor=conn.execute("SELECTid,name,address,salaryfromCOMPANY")In[4]:forrowincursor:

...:print(row)

...:print("ID=",row[0])

...:print("NAME=",row[1])

...:print("ADDRESS=",row[2])

...:print("SALARY=",row[3],"\n")

...:conn.close()(1,'Paul','California',25000.0)ID=1NAME=PaulADDRESS=CaliforniaSALARY=25000.0

(3,'Teddy','Norway',20000.0)ID=3NAME=TeddyADDRESS=NorwaySALARY=20000.0

(4,'Mark','Rich-Mond',65000.0)ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000.0也可以直接删除整张表：c