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