from sqlsetup import connect, run, commit, tbl, close
connect('sakila')
run("SELECT * FROM address")
tbl()
close()
connect('mydb')
run("SELECT * FROM player")
tbl()
run("DESC player")
close()
sel("SELECT * FROM department;")
sel("SELECT * FROM employee;")
sel("SELECT emp_id, 'Active', emp_id * .5, UPPER(lname) FROM employee")
sel("SELECT VERSION(), USER(), DATABASE();") # no FROM needed
sel("""SELECT UPPER(lname) AS last_name_upper,
emp_id AS employee_id FROM employee""")
sel("SELECT DISTINCT cust_id FROM account")
run("""CREATE VIEW employee_vw AS
SELECT emp_id, fname, lname, YEAR(start_date) start_year
FROM employee;""")
commit()
sel("""SELECT employee.emp_id, employee.fname,
department.name dept_name
FROM employee INNER JOIN department
ON employee.dept_id = department.dept_id;""")
sel("""SELECT emp_id, fname, start_date, title
FROM employee
WHERE title = 'Head Teller';""")
sel("""SELECT d.name, count(e.emp_id)
FROM department d INNER JOIN employee e
# open cmd
# cd \apache24
# httpd.exe
# import mysql.connector
# cnx = mysql.connector.connect(user='******', password='******', host='127.0.0.1', database='mydb')
# cursor = cnx.cursor()
# cursor.execute("""CREATE TABLE customer
# (cust_id SMALLINT,
# name VARCHAR(60) CHARACTER SET utf8,
# CONSTRAINT pk_customer PRIMARY KEY (cust_id));""")
# cursor.execute("SELECT * FROM corp;") # result is an iterable
# list(cursor)
# cursor.execute("INSERT INTO corp (corp_id, name) VALUES (12, 'Incorp');")
# cnx.commit()
from sqlsetup import connect, run, commit, tbl, close
connect('learning')
run("SELECT * FROM person")
tbl()
run("DESC person")
tbl()
close()
from sqlsetup import connect, run, commit, tbl, close, sel
connect('learning')
run("""CREATE TABLE person
(person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender ENUM('M','F'),
birth_date DATE,
street VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(30),
postal_code VARCHAR(10),
CONSTRAINT pk_person PRIMARY KEY (person_id));""")
run("DROP TABLE person;")
run("DESC person;")
run("""CREATE TABLE favorite_food
(person_id SMALLINT UNSIGNED,
food VARCHAR(20),
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
CONSTRAINT pk_fav_food_person_id FOREIGN KEY (person_id)
REFERENCES person (person_id));""")
run("DESC favorite_food")
tbl()
