Python编程：使用Sqlite3创建数据库、表和执行查询

今天学习使用Sqlite3创建数据库、表、运行查询，并记录学习过程欢迎大家一起交流分享。

首先新建一个python文件命名为employee.py类，定义类的基本属性方法:

class Employee(object): """do first,last,payring for Employee""" def __init__(self, first,last,pay): self.first = first self.last = last self.pay = pay @property def email(self): return '{}.{}@email.com'.format(self.first,self.last) @property def fullname(self): return '{} {}'.format(self.first,self.last) def __repr__(self): return "Employee('{}','{}',{})".format(self.first,self.last,self.pay)

接下来新建一个python文件命名为py3_sqlite3.py，在这个文件中进行操作代码编写:

#sqlite3是一个内置的python模块#使用该模块进行创建数据库，表，运行查询等#注：以下内容需要具有数据库知识基础import sqlite3#创建数据库并获得连接conn = sqlite3.connect('employee.db')#获得游标c = conn.cursor()#创建数据库表employeesc.execute("""CREATE TABLE IF NOT EXISTS employees( first TEXT, last TEXT, pay INTEGER )""")#提交事务conn.commit()conn.close()#定义一个函数获取数据库连接def getConn(): conn = sqlite3.connect('employee.db') return connconn = getConn()c = conn.cursor()#插入数据到表中#c.execute("INSERT INTO employees VALUES('yale','Andy',50000)")#c.execute("INSERT INTO employees VALUES('T','Bag',60000)")conn.commit()conn.close()#查询数据conn = getConn()c = conn.cursor()c.execute("SELECT * FROM employees WHERE first='yale' ")#获取一条数据 返回的是元组print(c.fetchone())#('yale', 'Andy', 50000)conn.commit()conn.close()#查询数据conn = getConn()c = conn.cursor()c.execute("SELECT * FROM employees")#获取所有数据print(c.fetchall())#[('yale', 'Andy', 50000), ('T', 'Bag', 60000)]conn.commit()conn.close()#接下来我们导入Employee类模块from employee import Employeeemp1 = Employee('Trace','Mack',10000)emp2 = Employee('Beast','Monster',87887)#插入emp1对象到数据库conn = getConn()c = conn.cursor()#插入数据到表中#c.execute("INSERT INTO employees VALUES('{}','{}',{})".format(emp1.first,emp1.last,emp1.pay))#或者使用？占位符#c.execute("INSERT INTO employees VALUES(?,?,?)",(emp1.first,emp1.last,emp1.pay))#或者名称占位：#c.execute("INSERT INTO employees VALUES(:first,:last,:pay)",{'first':emp1.first,'last':emp1.last,'pay':emp1.pay})conn.commit()conn.close()#接下来定义数据库增删该查函数进行操作#这里使用内存数据库conn = sqlite3.connect(':memory:')c = conn.cursor()c.execute("""CREATE TABLE employees ( first text, last text, pay integer )""")#添加员工函数def insert_emp(emp): with conn: c.execute("INSERT INTO employees VALUES (:first, :last, :pay)", {'first': emp.first, 'last': emp.last, 'pay': emp.pay})#查询员工函数def get_emps_by_name(lastname): c.execute("SELECT * FROM employees WHERE last=:last", {'last': lastname}) return c.fetchall()#更新员工函数def update_pay(emp, pay): with conn: c.execute("""UPDATE employees SET pay = :pay WHERE first = :first AND last = :last""", {'first': emp.first, 'last': emp.last, 'pay': pay})#删除员工函数def remove_emp(emp): with conn: c.execute("DELETE from employees WHERE first = :first AND last = :last", {'first': emp.first, 'last': emp.last})emp_1 = Employee('John', 'Doe', 80000)emp_2 = Employee('Jane', 'Doe', 90000)insert_emp(emp_1)insert_emp(emp_2)emps = get_emps_by_name('Doe')print(emps)update_pay(emp_2, 95000)remove_emp(emp_1)emps = get_emps_by_name('Doe')print(emps)conn.close()

今天初学python用Sqlite3创建数据库、表、运行查询学习就到这里！

关注公号yale记

下面的是我的公众号二维码图片，欢迎关注。