Python 学习第九步: 数据库

基于 sqlite 、 mysql的连接示例

python3 有自带的 sqlite3 模块以及 Sqlite3 数据库， 可以直接进行数据库程序设计。
对象连接方法
db.close()
db.commit()
db.cursor()
db.rollback()

游标对象的属性和方法
c --> cursor
c.arraysize  : fetchmany()返回的行数
c.close(): 游标超出范围外时自动执行此操作
c.description: 一个7元组， 描述了每个相继的游标c组成的列。
c.execute(sql,params)执行查询
c.executemany(sql, seq_of_params) seq_of_params为序列或者映射， 对其每一项执行sql （不适合select）
c.fetchall()  返回序列， 包括所有未取回的行
c.fetchmany(size)  返回一个行序列（每个行也是一个序列）， size 默认 = c.arraysize
c.fetchone(): 返回结果集的下一行， 或者 None， 没有结果集则为 异常。
c.rowcount: 最近一次操作影响的行数。

db = sqlite3.connect（filename）返回数据库对象病打开。
mycursor = db.cursor() 数据库操作通过游标完成

mycursor.execute("create table A ("
    "id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,"
    "name TEXT UNIQUE NOT NULL,"
    "PID INTEGER NOT NULL,"
    "FOREIGN KEY(PID) REFERENCES table_B)"
)

db.commit()

mycursor.execute("insert into A(name,PID)"
    "values(?,?,?)", ("ray",386396)) 使用 ？ 占位符， 后面必须为元组，注意一元组格式 （1,）
db.commit()

mycursor.execute("select * from A where name=?", ("ray",))
fields = mycursor.fetchone()
print(fields[0] if fields is not None else None)

或者用
mycursor.execute("select id,name,pid from A where name=:name", dict(name="ray",))
cid,cname,cpid = mycursor.fetchone()

db.close()

对于复杂一点的mysql， 附上以前的一个代码

# coding=UTF-8

import cv2
import numpy as np
from feature_get import FaceVgg
from similarity import cosine_similarity
import sys
from imp import reload
from struct import pack, unpack
from main import get_run_flags, set_run_flags
from queue import Queue
import time
import datetime
import threading
import udp_server

'''
map class to table of mysql
'''

from sqlalchemy import create_engine
from sqlalchemy import Column, String, Integer, BLOB
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine("mysql+pymysql://ray:hattie@localhost:3306/db_faces", max_overflow=5)
Base = declarative_base()
DBSession = sessionmaker(bind=engine)
session = DBSession()

class FaceItem(Base):
    __tablename__ = 'tblFaces'
    fPersonID = Column(Integer, primary_key=True, autoincrement=True)
    fGroup = Column(Integer, nullable=False)
    fPersonName = Column(String(32), nullable=False)
    bFeature = Column(BLOB, nullable=False)
    bImage = Column(BLOB, nullable=False)

queue_face_request_item = Queue(10)
def new_face_request_item(client_addr, requst_type, img, group, names, fSeq):
    global queue_face_request_item
    item = [client_addr, requst_type, img, group, names, fSeq]
    #queue_face_request_item.put(item)
    queue_face_request_item.put_nowait(item)


def face_feature_exec(params):
    global queue_face_request_item
    print (params)
    face_vgg_net = FaceVgg() # load caffe engine
    faceset = session.query(FaceItem).all() #load current dataset

    while not get_run_flags(): #not bTerminated:
        # get image from list, proc it , then send message back.
        try:
            item = queue_face_request_item.get()
#-------------------------------------------------------------------------------------------------------------------------
            if item[1] == udp_server.CMD_FACE_REQ:
                print("Face Recognize.")
                extract_start = datetime.datetime.now().microsecond
                feat_blob = face_vgg_net.extract_face_feat(item[2])
                extract_end = datetime.datetime.now().microsecond
                print(["### extract", extract_start - extract_end])

                max_score = 0.0
                row_idx = faceset[0]
                for row in faceset:
                    feat_list = []
                    fFeat = row.bFeature
                    i = 0
                    while i<4096 :
                        tmp = fFeat[i*4:i*4+4]
                        f_val = unpack('f', tmp)[0]
                        feat_list.append(f_val)
                        i = i+1

                    feat_db = np.array(feat_list)
                    extract_start = datetime.datetime.now().microsecond
                    similarity = cosine_similarity(feat_blob, feat_db)
                    extract_end = datetime.datetime.now().microsecond
                    if similarity>max_score:
                        print(["### compare", extract_start-extract_end])
                        max_score = similarity
                        row_idx = row

                if max_score > 0.60:
                    print( "best score is %f" % max_score )
                    udp_server.sendback_recognized_ok(item[0], item[5], row_idx.fGroup, row_idx.fPersonID, row_idx.fPersonName, max_score)


#-------------------------------------------------------------------------------------------------------------------------
            elif item[1] == udp_server.CMD_REGISTER:
                print("Face Register.")
                feat_blob = face_vgg_net.extract_face_feat(item[2])

                img_encode = cv2.imencode('.jpg', item[2])[1]
                data_img_encode = np.array(img_encode)
                img_blob = data_img_encode.tostring()

                row_item = FaceItem(fGroup=item[3], fPersonName=item[4], bFeature=feat_blob, bImage=img_blob)
                session.add(row_item)
                session.commit()
                #print(row_item.fPersonID)

                faceset = session.query(FaceItem).all() #load current dataset
                #print(len(faceset))

                #send OK to client. 0->clientaddr, 5->fseq
                udp_server.sendback_regist_ok(item[0], item[5])

                print("OK")

        except:
            sleep(0.01)


    set_run_flags(False)

# --------------------------------------------------------------------------------------------------
def main_thread():
    threads = [] # list
    thr_face_server = threading.Thread(target=face_feature_exec, args=(u'FaceReco 服务监听',))
    threads.append(thr_face_server)

    for t in threads:
        t.setDaemon(True)
        t.start()

    for t in threads:
        t.join()

    print("Thread Face is Over.\n")

'''
   循环遍历列表
'''
def main_1():
    faceset = session.query(FaceItem).all()

    fFeat = faceset[0].bFeature
    #每四個字節爲一个float类型
    print(fFeat)
    print(len(fFeat))
    i = 0
    feat_list = []
    while i<4096 :
        tmp = fFeat[i*4:i*4+4]
        f_val = unpack('f', tmp)[0]
        feat_list.append(f_val)
        i = i+1

    feat_1 = np.array(feat_list)
    print(feat_1)
    print(type(feat_1))

    fImage = faceset[0].bImage
    img_encode = np.fromstring(fImage, np.uint8)
    img_decode = cv2.imdecode(img_encode, 1)
    cv2.imshow('face', img_decode)
    cv2.waitKey()

'''
   add one record.
'''
def main_2():
    face_vgg_net = FaceVgg()

    img_1 = cv2.imread('img/0_2.jpg')
    feat_1 = face_vgg_net.extract_face_feat(img_1)
    print(feat_1)
    print(type(feat_1))


    img_encode = cv2.imencode('.jpg', img_1)[1]
    data_img_encode = np.array(img_encode)
    str_img = data_img_encode.tostring()

    session.query(FaceItem).delete()
    session.commit()

    item = FaceItem(fGroup=0, fPersonName='0_2', bFeature=feat_1, bImage=str_img)
    session.add(item)
    session.commit()
    print("OK")


if __name__ == '__main__':
    reload(sys)
    #main_2()
    #main_1()
    main_thread()