Chapter 11

Sqlite3

import sqlite3
conn = sqlite3.connect('mrsoft.db') 
cursor = conn.cursor()
cursor.execute('create table user (id int(10) primary key, name varchar(20))')
cursor.close()
conn.close()

User

import sqlite3
conn = sqlite3.connect('mrsoft.db')
cursor = conn.cursor()
cursor.execute('insert into user (id, name) values ("1","MRsoft")')
cursor.execute('insert into user (id, name) values ("2","Andy")')
cursor.execute('insert into user (id, name) values ("3","明日科技小助手")')
cursor.close()
conn.commit()
conn.close()

Fetchone

import sqlite3
conn = sqlite3.connect('mrsoft.db')
cursor = conn.cursor()
cursor.execute('select * from user')
result1 = cursor.fetchone()
print(result1)
cursor.close()
conn.close()

Update

import sqlite3
conn = sqlite3.connect('mrsoft.db')
cursor = conn.cursor()
cursor.execute('update user set name = ? where id = ?', ('MR',1))
cursor.execute('select * from user')
result = cursor.fetchall()
print(result)
cursor.close()
conn.commit()
conn.close()

Delete

import sqlite3
conn = sqlite3.connect('mrsoft.db')
cursor = conn.cursor()
cursor.execute('delete from user where id = ?', (1,))
cursor.execute('select * from user')
result = cursor.fetchall()
print(result)
cursor.close()
conn.commit()
conn.close()

Books

import pymysql
db = pymysql.connect(host = "localhost", user = "root", password = "13467958k", database = "localhost_3306")
cursor = db.cursor()
cursor.execute("SELECT VERSION")
data = cursor.fetchone()
print("Database version : %s" % data)
db.close()

Books 2

import pymysql
db = pymysql.connect(host = "localhost", user = "root", password = "13467958k", database = "localhost_3306")
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS books")
sql = """
CREATE TABLE books (
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    category varchar(50) NOT NULL,
    PRICE decimal(10,2) DEFAULT NULL,
    publish_time date DEFAULT NULL,
    PRIMARY KEY(id)
) ENGINE = MyISAM AUTO_INCERMENT = 1 DEFAULT CHARSET = utf8;
"""
cursor.execute(sql)
db.close()

Project

#实战1
import pymysql
db = pymysql.connect("localhost", "root", "root", "mrsoft",charset="utf8")
cursor = db.cursor()
cursor.execute("select name,price from books order by id ")
result = cursor.fetchall()
for book in result :
    print("图书：《{name}》, 价格：￥{price}元".format(name=book[0], price=book[1]))
db.close()

#实战2
import pymysql
db = pymysql.connect("localhost", "root", "root", "mrsoft",charset="utf8")
cursor = db.cursor()
cursor.execute("select name,price,publish_time from books where price < 70 and publish_time >= '2017-01-01' ")
result = cursor.fetchall()
for book in result :
    print("图书：《{name}》, 价格：￥{price}元，出版日期：{publish_time}".format(name=book[0], price=book[1],publish_time=book[2]))
# 关闭数据库连接
db.close()

#实战3
import pymysql
db = pymysql.connect("localhost", "root", "root", "mrsoft",charset="utf8")
cursor = db.cursor()
try:
    cursor.execute("delete from books where category = 'PHP' ")
    db.commit()
except:
    db.rollback()
cursor.execute("select name,price from books")
result = cursor.fetchall()
for book in result :
    print("图书：《{name}》, 价格：￥{price}元".format(name=book[0], price=book[1]))
db.close()