Python 数据库操作

      Python中提供了SQlite和ＭySQL这两种数据库的接口。SQLite是一个轻量级的关系型数据库。这一数据库使用SQL语言。SQLite作为后端数据库，可以搭配Python建网站，或者制作有数据存储需求的工具。SQLite还在其它领域有广泛的应用，比如HTML5和移动端。Python标准库中的sqlite3提供该数据库的接口。

     Python操作数据库过程有：

     步骤1:使用db.connect创建数据库连接，返回连接对象为conn

     步骤2:操作数据库中的记录

               1  使用conn.cursor创建游标对象cu

                2 通过cu.execute查询数据库，用cu.fetchall，fetchone和fetchmany等方法查询数据库，并返回结果

                3 使用cu.commit修改数据库

      步骤3:关闭cu 和conn

 创建数据库 

     首先来创建数据库，以及数据库中的表。在使用connect()连接数据库后，我就可以通过定位指针cursor，来执行SQL命令：

import sqlite3
# test.db is a file in the working directory.
conn = sqlite3.connect("test.db")
c = conn.cursor()
# create tables
c.execute('''create table if not exists category
      (id int primary key, sort int, name text)''')
c.execute('''create table if not exists book
      (id int primary key, 
       sort int, 
       name text, 
       price real, 
       category int,
       FOREIGN KEY (category) REFERENCES category(id))''')

# save the changes
conn.commit()

# close the connection with the database
conn.close()

      数据库"test.db"连接成功之后，就可以创建数据库中的表（table）了，我们在创建表的时候，使用“create table if not exists”字段，如果表格不存在，则创建。

 插入数据

       上面创建了数据库和表，确立了数据库的抽象结构。下面将在同一数据库中插入数据：   

import sqlite3
conn = sqlite3.connect("test.db")
c    = conn.cursor()
books = [(1, 1, 'Cook Recipe', 3.12, 1),
            (2, 3, 'Python Intro', 17.5, 2),
            (3, 2, 'OS Intro', 13.6, 2),
           ]

# execute "INSERT" 
c.execute("INSERT INTO category VALUES (1, 1, 'kitchen')")
# using the placeholder
c.execute("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])
# execute multiple commands
c.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)

conn.commit()
conn.close()

      插入数据同样可以使用execute()来执行完整的SQL语句。SQL语句中的参数，使用"?"作为替代符号，并在后面的参数中给出具体值。这里不能用Python的格式化字符串，如"%s"，因为这一用法容易受到SQL注入攻击，并且在执行时，也是容易出错的。
     我也可以用executemany()的方法来执行多次插入，增加多个记录。每个记录是表中的一个元素，如上面的books表中的元素。

查询

     在执行查询语句后，Python将返回一个循环器，包含有查询获得的多个记录。你循环读取，也可以使用sqlite3提供的fetchone()和fetchall()方法读取记录：      

   

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
# retrieve one record
c.execute('SELECT name FROM category ORDER BY sort')
print(c.fetchone())
print(c.fetchone())

# retrieve all records as a list
c.execute('SELECT * FROM book WHERE book.category=1')
print(c.fetchall())

# iterate through the records
for row in c.execute('SELECT name, price FROM book ORDER BY sort'):
    print(row)

更新与删除

     更新使用UPDATE，删除使用DELETE关键字。

conn = sqlite3.connect("test.db")
c = conn.cursor()
c.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))
c.execute('DELETE FROM book WHERE id=2')

conn.commit()
conn.close()

    你也可以直接删除整张表：

c.execute('DROP TABLE book')

 增加列

     如果需要在表中新增列，例如在表 book，新增一列，使用ALTER关键字。

sql="ALTER TABLE %s  ADD %s integer default 0" %(book,"Print Time")
self._cursor.execute(sql)
self._connect.commit()

     其中default 0是设置默认值。

     如果需要获取数据库每列的名称，需要使用如下：

self._connect=sqlite3.connect("test.db")
self._cursor=self._connect.cursor()
sql="select * from %s;" %db_name
self._cursor.execute(sql)
self._connect.commit()
col_name_list = [tuples[0] for tuples in self._cursor.description] 

数据库操作遇到的问题：

       关于数据库多线程写的时候需要注意，数据库可以多线程同时读，但是不可以多线程同时写，如果多线程同时写的时候，就会下面的问题：

OperationalError: database is locked

       因此需要在数据库连接的时候，加上超时的操作，即，当某个线程写时，加上参数："PRAGMA busy_timeout = 30000" ，会最多等待多少ms。

        db=self.__db_path + self.db_name+".db"
        self._connect=sqlite3.connect(db,check_same_thread=False)
        self._connect.execute("PRAGMA busy_timeout = 30000")#30s
        self._cursor=self._connect.cursor()

    关于PRAGMA busy_timeout的解释链接如下：http://www.sqlite.org/pragma.html#pragma_busy_timeout