位置：首頁 > 數據庫 > SQLite教學 > SQLite Python

SQLite Python

 上一篇

sqlite3 可以與Python sqlite3 模塊集成是由格哈德·哈林（Gerhard Haring）編寫。 PEP249所描述的DB-API2.0規範，它提供了一個SQL接口兼容。不需要單獨安裝這個模塊，因為它默認情況下隨著Python版本在2.5.x 一起安裝。

要使用sqlite3模塊，必須首先創建一個連接對象，表示數據庫中，然後可以選擇創建遊標對象，這將幫助在執行的所有SQL語句。

Python sqlite3 模塊API

以下是重要的sqlite3模塊程序，它可以足夠Python程序SQLite數據庫操作工作。如果要尋找一個更複雜的應用程序，那麼你可以看看成的Python sqlite3 模塊的官方文檔。

S.N. API & 描述
1 sqlite3.connect(database [,timeout ,other optional arguments])

This API opens a connection to the SQLite database file database. You can use ":memory:" to open a database connection to a database that resides in RAM instead of on disk. If database is opened successfully, it returns a connection object.

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

If given database name does not exist then this call will create the database. You can specify filename with required path as well if you want to create database anywhere else except in current directory.
2 connection.cursor([cursorClass])

This routine creates a cursor which will be used throughout of your database programming with Python. This method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends sqlite3.Cursor.
3 cursor.execute(sql [, optional parameters])

This routine executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks and named placeholders (named style).

For example:cursor.execute("insert into people values (?, ?)", (who, age))
4 connection.execute(sql [, optional parameters])

This routine is a shortcut of the above execute method provided by cursor object and it creates an intermediate cursor object by calling the cursor method, then calls the cursor's execute method with the parameters given.
5 cursor.executemany(sql, seq_of_parameters)

This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql.
6 connection.executemany(sql[, parameters])

This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor.s executemany method with the parameters given.
7 cursor.executescript(sql_script)

This routine executes multiple SQL statements at once provided in the form of script. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. All the SQL statements should be separated by semi colon (;).
8 connection.executescript(sql_script)

This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor's executescript method with the parameters given.
9 connection.total_changes()

This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened.
10 connection.commit()

This method commits the current transaction. If you don.t call this method, anything you did since the last call to commit() is not visible from other database connections.
11 connection.rollback()

This method rolls back any changes to the database since the last call to commit().
12 connection.close()

This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost!
13 cursor.fetchone()

This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available.
14 cursor.fetchmany([size=cursor.arraysize])

This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter.
15 cursor.fetchall()

This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available.

連接到數據庫

Python代碼顯示了如何連接到一個現有的數據庫。如果數據庫不存在，那麼它就會被創建，終於將返回一個數據庫對象。

#!/usr/bin/python

import sqlite3

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

print "Opened database successfully";

在這裡，您還可以提供特殊的名字 :memory: 在RAM中創建一個數據庫的數據庫名稱。現在，讓我們運行上麵的程序在當前目錄中創建數據庫test.db。按要求，你可以改變路徑。上麵的代碼在sqlite.py文件並執行它，如下圖所示。如果數據庫創建成功，那麼它會給以下消息：

$chmod +x sqlite.py
$./sqlite.py
Open database successfully

創建表

以下Python程序將使用以前創建的數據庫中創建一個表：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print "Table created successfully";

conn.close()

上述程序執行時，它會創建表COMPANYtest.db的，它會顯示以下消息：

Opened database successfully
Table created successfully

INSERT 操作

Python程序顯示了我們如何能夠創建表COMPANY 在上麵的例子中創建表中的記錄：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) 
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) 
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) 
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) 
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

上述程序執行時，它會創建COMPANY表中的記錄，並顯示以下兩行：

Opened database successfully
Records created successfully

SELECT 操作

Python程序，表明我們如何能夠獲取並顯示COMPANY 在上麵的例子中創建表的記錄：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "
"

print "Operation done successfully";
conn.close()

當上述程序執行時，它會產生以下結果：

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

UPDATE 操作

Python代碼顯示如何，我們可以使用UPDATE語句來更新任何記錄，然後獲取並顯示更新的記錄，從COMPANY 表：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit
print "Total number of rows updated :", conn.total_changes

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "
"

print "Operation done successfully";
conn.close()

當上述程序執行時，它會產生以下結果：

Opened database successfully
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

DELETE 操作

Python代碼顯示了如何我們可以使用DELETE語句刪除任何記錄，然後獲取並顯示剩餘的記錄COMPANY 表：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("DELETE from COMPANY where ID=2;")
conn.commit
print "Total number of rows deleted :", conn.total_changes

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "
"

print "Operation done successfully";
conn.close()

當上述程序執行時，它會產生以下結果：

Opened database successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully


 上一篇
極客書 © 2015-2020 . Email: