python sqlite学习

python sqlite 简单学习 与 简单类的编写

[title]约束[/title]
创建表时添加的约束条件，约束整个表，不提供后期删除约束的操作

sqlite 支持 alter table 子集，只能重命名表或者添加新列，不能给删除列，重命名列，或者为已有的表添加或删除约束

NOT NULL：不能为NULL
UNIQUE：确保值不同
PRIMARY Key：唯一标识表行的记录
DEFAULT：不给赋值时自动提供默认值 # SALARY REAL DEFAULT 50000.00
CHECK：保证某列所有值满足一定条件 # SALARY REAL CHECK(SALARY > 0)

[title]python 简单举例[/title]

• 建表
• 提交
• 去重约束操作
• select打印

import sqlite3

sqlconn=sqlite3.connect('tt.db')
sqlcur=sqlconn.cursor()

def safeins(a,b):
    try:
        a(b)
    except Exception,e:
        print e

safeins(sqlcur.execute,'create table nnn(name text unique,id integer);')

safeins(sqlcur.execute,'select * from nnn;')
print sqlcur.fetchall()

safeins(sqlcur.execute,'insert into nnn values("5",5)')
safeins(sqlcur.execute,'insert into nnn values("2",2)')
safeins(sqlcur.execute,'insert into nnn values("3",3)')
safeins(sqlcur.execute,'insert into nnn values("3",3)')
safeins(sqlcur.execute,'insert into nnn values("6",6)')
safeins(sqlcur.execute,'insert into nnn values("4",4)')

safeins(sqlcur.execute,'select * from nnn;')
print sqlcur.fetchall()

sqlcur.close()
sqlconn.commit()
sqlconn.close()

[title]python自己实现类[/title]

2017年1月6日17:59:34 基本功能

import sqlite3

class jlsql(object):
    def op(self,func,para):
        '''do safe operation'''
        try:
            func(para)
            return 1
        except Exception as e:
#             print(para)
#             print(e)
            return 0;
            
    def __init__(self,databases):
        try:
            self.conn=sqlite3.connect(databases,check_same_thread = False)
            self.conn.isolation_level = None
            self.cur=self.conn.cursor()
        except Exception as e:
            print("Init databases failed!")
            print(e)
            exit(0);
    def execute(self,cmd):
        '''cursor execute wite safe operation'''
        return self.op(self.cur.execute,cmd)
    
    def select(self,cmd):
        '''cursor execute wite safe operation'''
        if self.op(self.cur.execute,cmd):
            return self.cur.fetchall()
        else:
            return [];
    
    def commit(self):
        self.conn.commit()
        
    def close(self):
        self.cur.close()
        self.conn.commit()
        self.conn.close()
    
    def close_unsave(self):
        self.cur.close()
        self.conn.close()

            
sql=jlsql('tt.db')
sql.execute('create table sss (id integer primary key , name text unique );')
sql.execute('insert into sss values("8",8);')
sql.execute('insert into sss values("7",8);')
sql.execute('insert into sss values("8",7);')
sql.execute('update sss set id=%s where name=%s;'%(223,"8"))
print(sql.select('select * from sss;'))
sql.close()