以SQLite和PySqlite為例來學(xué)習(xí)Python DB API

更新時(shí)間：2020年02月05日 19:32:51 投稿：mdxy-dxy

本文將以SQLite和PySqlite為例來學(xué)習(xí)Python DB API，pysqlite是一個(gè)sqlite為python 提供的api接口，它讓一切對(duì)于sqlit的操作都變得異常簡(jiǎn)單

Python應(yīng)用編程需要用到的針對(duì)不同數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)接口：http://wiki.python.org/moin/DatabaseInterfaces

Python標(biāo)準(zhǔn)的DB API 2.0見：http://www.python.org/dev/peps/pep-0249/

本文將以SQLite和PySqlite為例來學(xué)習(xí)Python DB API。

pysqlite是一個(gè)sqlite為python 提供的api接口，它讓一切對(duì)于sqlit的操作都變得異常簡(jiǎn)單。

從Python2.5起，pysqlite作為Python的一個(gè)標(biāo)準(zhǔn)模塊。在使用標(biāo)準(zhǔn)庫(kù)時(shí)，它被簡(jiǎn)稱為sqlite3模塊。

sqlite3標(biāo)準(zhǔn)庫(kù)，詳見：http://docs.python.org/3.3/library/sqlite3.html

基本的學(xué)習(xí)內(nèi)容如下：

1.創(chuàng)建一張表

# filename:create.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# 創(chuàng)建數(shù)據(jù)表的sql語(yǔ)句
createtb_sql = """create table test(
id integer,
name text,
age integer);"""

# 調(diào)用execute()執(zhí)行create_sql語(yǔ)句
cur.execute(createtb_sql)

# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

2.簡(jiǎn)單的插入數(shù)據(jù)

# filename:insert.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# 向數(shù)據(jù)表中插入數(shù)據(jù)的sql語(yǔ)句
'''
insert_sql = """
insert into test values(1, 'huhu', 20);
insert into test values(2, 'hengheng', 18);
insert into test values(3, 'huahua', 18);
"""
'''

insert_sql = """
insert into test values(1, 'huhu', 20);
"""

# 調(diào)用execute()執(zhí)行insert sql語(yǔ)句
# execute一次只能執(zhí)行一條語(yǔ)句
cur.execute(insert_sql)

# 提交事務(wù)
conn.commit()
# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

3.查詢

# filename:select.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# 查詢數(shù)據(jù)表的sql語(yǔ)句
select_sql = """ select * from test;"""

# 調(diào)用execute()執(zhí)行select sql語(yǔ)句
cur.execute(select_sql)

'''
while True:
  # fetchone()把查詢的結(jié)果集的下一行作為序列或者None
  row = cur.fetchone()
  if row == None:
    break
  print(row)
'''

'''
# fetchall()把查詢的結(jié)果集的所有行作為序列的序列
for row in cur.fetchall():
  print(row)
'''

# 迭代對(duì)象遍歷
for row in cur:
  print(row)

# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

4.刪除數(shù)據(jù)

# filename:delete.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# delete語(yǔ)句
delete_sql = """delete from test"""

# execute()執(zhí)行sql語(yǔ)句
cur.execute(delete_sql)

# commit()提交事務(wù)
conn.commit()

# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

以上四步的運(yùn)行結(jié)果：

5.一次插入多條數(shù)據(jù)

# filename:insertmany.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# 向數(shù)據(jù)表中插入數(shù)據(jù)的sql語(yǔ)句
insert_sql = """insert into test values(?, ?, ?)"""

# 調(diào)用execute()執(zhí)行insert sql語(yǔ)句
# execute一次只能執(zhí)行一條語(yǔ)句
for line in open('E:/code/py/db/data.txt'):
  fields = line.split(',')
  vals = [f for f in fields]
  cur.execute(insert_sql,vals)

# 提交事務(wù)
conn.commit()
# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

data.txt：

1,huhu,18
2,hengheng,18
3,lq,20

運(yùn)行結(jié)果：

6.插入數(shù)據(jù)的方法（參數(shù)綁定，executemany的使用）：

# inserts.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# 向數(shù)據(jù)表中插入數(shù)據(jù)的sql語(yǔ)句
# 最簡(jiǎn)單的insert形式
insert_sql1 = """insert into test values(1, 'huhu', 20);"""
# execute()一次只能執(zhí)行一條語(yǔ)句
cur.execute(insert_sql1)

# 參數(shù)綁定
# execute()第二個(gè)參數(shù)：位置參數(shù)或者字典類型參數(shù)
insert_sql2 = """insert into test values(?, ?, ?)"""
cur.execute(insert_sql2, (2,'hengheng',18))
insert_sql3 = """insert into test values(:id, :name, :age)"""
cur.execute(insert_sql3, {'id':3, 'name':'lq', 'age':18})

# executemany()第二個(gè)參數(shù)：列表類型參數(shù)，適用于迭代器和生成器
l = [(4, 'huhu', 18), (5, 'hh', 18), (6, 'lq', 18)]
cur.executemany(insert_sql2, l)

# 利用生成器實(shí)現(xiàn)
def l_generator():
  l = [(7, 'huhu', 18), (8, 'hh', 18), (9, 'lq', 18)]
  for t in l:
    yield(t)

cur.executemany(insert_sql2, l_generator())

# 提交事務(wù)
conn.commit()
# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

運(yùn)行結(jié)果：

7.帶條件的的update、delelte和select語(yǔ)句

（1）update

# filename:update.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# update語(yǔ)句
update_sql = """update test set name = 'noname' where id = ?"""

# execute()和executem()執(zhí)行sql語(yǔ)句
x = (1, )
cur.execute(update_sql, x)
y = (2, )
cur.execute(update_sql, y)
l = [(3, ),(4, ),(5, )]
cur.executemany(update_sql, l)

# commit()提交事務(wù)
conn.commit()

# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

運(yùn)行結(jié)果：

（2）delete

# filename:delete1.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# delete語(yǔ)句
delete_sql = """delete from test where id = ?"""

# execute()和executemany()執(zhí)行sql語(yǔ)句
cur.execute(delete_sql, (1, ))
cur.executemany(delete_sql, [(2, ), (3, )])

# commit()提交事務(wù)
conn.commit()

# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

運(yùn)行結(jié)果：

（3）select

# filename:select1.py
import sqlite3

# 創(chuàng)建連接對(duì)象
conn = sqlite3.connect('E:/code/py/db/test.db')

# 創(chuàng)建一個(gè)游標(biāo)對(duì)象
cur = conn.cursor()

# 查詢數(shù)據(jù)表的sql語(yǔ)句
select_sql = """ select * from test where id = ?;"""
# 調(diào)用execute()執(zhí)行select sql語(yǔ)句
x = (8, )
cur.execute(select_sql, x)

'''
# 在executemany中，不能執(zhí)行select語(yǔ)句
y = [(2, ), (3, )]
cur.executemany(select_sql, y)
'''

# 迭代對(duì)象遍歷
for row in cur:
  print(row)

# 關(guān)閉游標(biāo)
cur.close()
# 關(guān)閉連接
conn.close()

運(yùn)行結(jié)果：

sqlite3標(biāo)準(zhǔn)庫(kù)相比Python DB API 2.0，增加了一個(gè)較為方便的函數(shù)executescript函數(shù)（一次可以執(zhí)行多條sql），介紹如下：

This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter.

sql_script can be an instance of str or bytes.

Example:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
  create table person(
    firstname,
    lastname,
    age
  );

  create table book(
    title,
    author,
    published
  );

  insert into book(title, author, published)
  values (
    'Dirk Gently''s Holistic Detective Agency',
    'Douglas Adams',
  );
  """)

好了這篇文章就為大家介紹到這了，希望大家以后多多支持腳本之家。

您可能感興趣的文章: