简单的sqlite3数据库操作实例

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

import sqlite3

class database:
    def __init__(self,**kwargs):
        self.filename = kwargs.get('filename')
        self.table = kwargs.get('table','test')
    
    def sql_do(self,sql,*params):
        self._db.execute(sql,params)
        self._db.commit()
    
    def insert(self,row):
        self._db.execute('insert into {} (t1,i1) values (?,?)'.format(self._table),(row['t1'],row['i1']))
        self._db.commit()
    
    def retrieve(self,key):
        cursor = self._db.execute('select * from {} where t1 = ?'.format(self._table),(key,))
        return dict(cursor.fetchone())
    
    def update(self,row):
        self._db.execute(
            'update {} set i1 = ? where t1 = ?'.format(self._table),(row['i1'],row['t1']))
        self._db.commit()
    
    def delete(self,key):
        self._db.execute('delete from {} where t1 = ?'.format(self._table),))
        self._db.commit()

    def disp_rows(self):
        cursor = self._db.execute('select * from {} order by t1'.format(self._table))
        for row in cursor:
            print('  {}: {}'.format(row['t1'],row['i1']))

    def __iter__(self):
        cursor = self._db.execute('select * from {} order by t1'.format(self._table))
        for row in cursor:
            yield dict(row)

    @property
    def filename(self): return self._filename

    @filename.setter
    def filename(self,fn):
        self._filename = fn
        self._db = sqlite3.connect(fn)
        self._db.row_factory = sqlite3.Row

    @filename.deleter
    def filename(self): self.close()

    @property
    def table(self): return self._table
    @table.setter
    def table(self,t): self._table = t
    @table.deleter
    def table(self): self._table = 'test'

    def close(self):
            self._db.close()
            del self._filename

def main():
    db = database(filename = 'test.db',table = 'test')

    print('Create table test')
    db.sql_do('drop table if exists test')
    db.sql_do('create table test ( t1 text,i1 int )')

    print('Create rows')
    db.insert(dict(t1 = 'one',i1 = 1))
    db.insert(dict(t1 = 'two',i1 = 2))
    db.insert(dict(t1 = 'three',i1 = 3))
    db.insert(dict(t1 = 'four',i1 = 4))
    for row in db: print(row)

    print('Retrieve rows')
    print(db.retrieve('one'),db.retrieve('two'))

    print('Update rows')
    db.update(dict(t1 = 'one',i1 = 101))
    db.update(dict(t1 = 'three',i1 = 103))
    for row in db: print(row)

    print('Delete rows')
    db.delete('one')
    db.delete('three')
    for row in db: print(row)

if __name__ == "__main__": main()

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!