一、 数据库
使用sqlite3,这是python内置的无需另外安装
import sqlite3
使用数据库五个步骤:
- 连接到数据库 connect to the database
conn = sqlite3.connect("lite.db")
#若不存在该数据库,将建立
- 建立一个cursor的游标对象 create a cursor object
cur = conn.cursor()
- 写一个SQL的query语句 write a SQL query
cur.execute("CREAT TABLE store (item TEXT, quantity INTEGER, price REAL)")
#建立一个名为store的表,item是text类型,quantity是integer类型,price是real类型
- 提交更改 commit changes
conn.commit()
- 关闭连接 close the connection
conn.close()
运行代码,运行成功会出现一个lite.db文件,如果你的browser不显示,再运行一次本代码,出现以下信息说明运行成功
sqlite3.OperationalError: table store already exists
数据库操作语句:
- 加入条件
CREATE TABEL IF NOT EXISTS store()
- 插入条目
- 查询
- 删除
- 更新
数据库基本函数封装
def create_table():
conn = sqlite3.connect("lite.db")
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS store(item TEXT, quantity INTEGER , price REAL)")
conn.commit()
conn.close()
def insert(item, quantity, price):
conn = sqlite3.connect("lite.db")
cur = conn.cursor()
# cur.execute("INSERT TO store VALUES('Wine Glass',8,10.5)")
cur.execute("INSERT INTO store VALUES(?,?,?)",(item, quantity, price))
conn.commit()
conn.close()
def view():
conn = sqlite3.connect("lite.db")
cur = conn.cursor()
cur.execute("SELECT * FROM store")
rows = cur.fetchall()
conn.close()
return rows# 返回值是list类型数据
def delete(item):
conn = sqlite3.connect("lite.db")
cur = conn.cursor()
cur.execute("DELETE FROM store WHERE item=?", (item,))
# cur.execute("DELETE FROM store WHERE item=?", item)
conn.commit()
conn.close()
问题整理1
cur.execute("DELETE FROM store WHERE item=?", item)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 6 supplied.
解决方案:
cur.execute("DELETE FROM store WHERE item=?", (item,))
def update(quantity,price,item):
conn = sqlite3.connect("lite.db")
cur = conn.cursor()
cur.execute("UPDATE store SET quantity=?,price=?,WHERE item=?",(quantity,price,item))
conn.commit()
conn.close()
使用第三方数据库工具代码,例如mysql等,只更改一小部分
import psycopg2
使用数据库五个步骤:
- 连接到数据库 connect to the database
conn = psycopg2.connect("dbname='lite.db' user='' password= '' host = '' port=''")
#若不存在该数据库,将建立()
- 建立一个cursor的游标对象 create a cursor object
cur = conn.cursor()
- 写一个SQL的query语句 write a SQL query
cur.execute("CREAT TABLE store (item TEXT, quantity INTEGER, price REAL)")
#建立一个名为store的表,item是text类型,quantity是integer类型,price是real类型
- 提交更改 commit changes
conn.commit()
- 关闭连接 close the connection
conn.close()
运行代码,运行成功会出现一个lite.db文件,如果你的browser不显示,再运行一次本代码,出现以下信息说明运行成功
sqlite3.OperationalError: table store already exists
数据库操作语句:
- 加入条件
CREATE TABEL IF NOT EXISTS store()
- 插入条目
- 查询
- 删除
- 更新
数据库基本函数封装
def create_table():
conn = psycopg2.connect("dbname='lite.db' user='' password= '' host = '' port=''")
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS store(item TEXT, quantity INTEGER , price REAL)")
conn.commit()
conn.close()
def insert(item, quantity, price):
conn = psycopg2.connect("dbname='lite.db' user='' password= '' host = '' port=''")
cur = conn.cursor()
# cur.execute("INSERT TO store VALUES('Wine Glass',8,10.5)")
cur.execute("INSERT INTO store VALUES(%s,%s,%s)",(item, quantity, price))
conn.commit()
conn.close()
def view():
conn = psycopg2.connect("dbname='lite.db' user='' password= '' host = '' port=''")
cur = conn.cursor()
cur.execute("SELECT * FROM store")
rows = cur.fetchall()
conn.close()
return rows# 返回值是list类型数据
def delete(item):
conn = psycopg2.connect("dbname='lite.db' user='' password= '' host = '' port=''")
cur = conn.cursor()
cur.execute("DELETE FROM store WHERE item=%s", (item,))
# cur.execute("DELETE FROM store WHERE item=?", item)
conn.commit()
conn.close()
def update(quantity,price,item):
conn = psycopg2.connect("dbname='lite.db' user='' password= '' host = '' port=''")
cur = conn.cursor()
cur.execute("UPDATE store SET quantity=%s,price=%s,WHERE item=%s",(quantity,price,item))
conn.commit()
conn.close()