pymysql 增删改查

pymysql创建据库连接
import pymysql
db = pymysql.connect(host='localhost', user='root', passwd='root', db='mrsoft') # 加上参数名，这样不会报错
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
date = cursor.fetchone()
print(date)
db.close()

pymysql创建一个图书books

import pymysql
db = pymysql.connect(host="localhost", user="root", passwd="root", database="mrsoft")
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_INCREMENT =1 DEFAULT CHARSET=utf8 ;
"""
cursor.execute(sql)
db.close()

pymysql在图书表中添加数据

import pymysql

db = pymysql.connect(host="localhost", user="root", passwd="root", database="mrsoft")
cursor = db.cursor()
data = [("零基础学Python", "Python", "79.80", "2018-5-20"),
        ("Python入门到精通", "Python", "69.80", "2018-6-18"),
        ("零基础学php", "php", "59.80", "2017-5-28"),
        ("零基础学Java", "Java", "79.80", "2017-5-11")]
try:
    cursor.executemany("insert into books(name , category, price, publish_time) "
                       "values (%s,%s,%s,%s)", data)
    db.commit()
except:
    db.rollback()
db.close()

pymysql查询日期大于某时间，并价格大于某价格

import pymysql
from datetime import date
db =pymysql.connect(host="localhost", user="root", passwd="root", database="mrsoft")
cursor = db.cursor()
cursor.execute("select * from books where  price >70 and publish_time>'2018-1-1'  ")
data = cursor.fetchall()
print(data)
db.close()