(四十六) 初遇python之Sqlite3创建数据库、表、运行查询

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)

#sqlite3是一个内置的python模块
#使用该模块进行创建数据库，表，运行查询等
#注：以下内容需要具有数据库知识基础
import sqlite3
#创建数据库并获得连接
conn = sqlite3.connect('employee.db')
#获得游标
c = conn.cursor()
#创建数据库表employees
c.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 conn




conn = 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 Employee

emp1 = 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()