Python - SQLite
** SQLite : Complete Overview - Creating a Database, Table, and Running Queries
==========================================
import sqlite3
conn = sqlite3.connect('employee.db')
==========================================
import sqlite3
conn = sqlite3.connect('employee.db')
c = conn.cursor()
c.execute("""CREATE TABLE employees (
first text,
last text,
pay integer
)""")
conn.commit()
conn.close()
---------------------------------
c.execute("""CREATE TABLE employees (
sqlite3.OperationalError: table employees already exists
---------------------------------
==========================================
import sqlite3
conn = sqlite3.connect('employee.db')
c = conn.cursor()
c.execute("SELECT * FROM employees WHERE last='Schafer'")
print(c.fetchone())
conn.commit()
conn.close()
---------------------------------
('Corey', 'Schafer', 50000)
---------------------------------
==========================================
import sqlite3
conn = sqlite3.connect('employee.db')
c = conn.cursor()
c.execute("SELECT * FROM employees WHERE last='Smith'")
print(c.fetchone())
conn.commit()
conn.close()
---------------------------------
None
---------------------------------
==========================================
import sqlite3
conn = sqlite3.connect('employee.db')
c = conn.cursor()
c.execute("SELECT * FROM employees WHERE last='Schafer'")
print(c.fetchall())
conn.commit()
conn.close()
---------------------------------
[('Corey', 'Schafer', 50000)]
---------------------------------
==========================================
import sqlite3
conn = sqlite3.connect('employee.db')
c = conn.cursor()
c.execute("INSERT INTO employees VALUES ('Mary', 'Schafer', 70000)")
conn.commit()
c.execute("SELECT * FROM employees WHERE last='Schafer'")
print(c.fetchall())
conn.commit()
conn.close()
---------------------------------
[('Corey', 'Schafer', 50000), ('Mary', 'Schafer', 70000)]
---------------------------------
==========================================
import sqlite3
from employee import Employee
conn = sqlite3.connect('employee.db')
c = conn.cursor()
emp_1 = Employee('John', 'Doe', 80000)
emp_2 = Employee('Jane', 'Doe', 90000)
print(emp_1.first)
print(emp_1.last)
print(emp_1.pay)
c.execute("SELECT * FROM employees WHERE last='Schafer'")
print(c.fetchall())
conn.commit()
conn.close()
---------------------------------
John
Doe
80000
[('Corey', 'Schafer', 50000), ('Mary', 'Schafer', 70000)]
---------------------------------
==========================================
import sqlite3
from employee import Employee
conn = sqlite3.connect('employee.db')
c = conn.cursor()
emp_1 = Employee('John', 'Doe', 80000)
emp_2 = Employee('Jane', 'Doe', 90000)
# c.execute("INSERT INTO employees VALUES ('{}', '{}', '{})".format(emp_1.first, emp_1.last, emp_1.pay))
c.execute("INSERT INTO employees VALUES (?, ?, ?)", (emp_1.first, emp_1.last, emp_1.pay))
conn.commit()
c.execute("INSERT INTO employees VALUES (:first, :last, :pay)", {'first': emp_2.first, 'last': emp_2.last, 'pay': emp_2.pay})
conn.commit()
c.execute("SELECT * FROM employees WHERE last='Schafer'")
print(c.fetchall())
conn.commit()
conn.close()
---------------------------------
[('Corey', 'Schafer', 50000), ('Mary', 'Schafer', 70000)]
---------------------------------
==========================================
import sqlite3
from employee import Employee
conn = sqlite3.connect('employee.db')
c = conn.cursor()
emp_1 = Employee('John', 'Doe', 80000)
emp_2 = Employee('Jane', 'Doe', 90000)
c.execute("SELECT * FROM employees WHERE last=?", ('Schafer',))
print(c.fetchall())
c.execute("SELECT * FROM employees WHERE last=:last", {'last': 'Doe'})
print(c.fetchall())
conn.commit()
conn.close()
---------------------------------
[('Corey', 'Schafer', 50000), ('Mary', 'Schafer', 70000)]
[('John', 'Doe', 80000), ('Jane', 'Doe', 90000)]
---------------------------------
==========================================
import sqlite3
from employee import Employee
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute("""CREATE TABLE employees (
first text,
last text,
pay integer
)""")
emp_1 = Employee('John', 'Doe', 80000)
emp_2 = Employee('Jane', 'Doe', 90000)
# c.execute("INSERT INTO employees VALUES ('{}', '{}', '{})".format(emp_1.first, emp_1.last, emp_1.pay))
c.execute("INSERT INTO employees VALUES (?, ?, ?)", (emp_1.first, emp_1.last, emp_1.pay))
conn.commit()
c.execute("INSERT INTO employees VALUES (:first, :last, :pay)", {'first': emp_2.first, 'last': emp_2.last, 'pay': emp_2.pay})
conn.commit()
# c.execute("SELECT * FROM employees WHERE last='Schafer'")
c.execute("SELECT * FROM employees WHERE last=?", ('Schafer',))
print(c.fetchall())
c.execute("SELECT * FROM employees WHERE last=:last", {'last': 'Doe'})
print(c.fetchall())
conn.commit()
conn.close()
---------------------------------
[]
[('John', 'Doe', 80000), ('Jane', 'Doe', 90000)]
---------------------------------
==========================================
import sqlite3
from employee import Employee
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()
---------------------------------
[('John', 'Doe', 80000), ('Jane', 'Doe', 90000)]
[('Jane', 'Doe', 95000)]
---------------------------------