一.数据库的基本操作
1.creat
- insert into table_name(column1,column2…) values(value1,value2…)
2.read
- select column_name from table_name
3.update
- update table_name set column1=value1,column2=value2 where some_column =some_value
4.delete
- delete from table_name where some_column=some_value
二.Python之SQLite操作
1)python之基本操作
1.连接数据库
- conn = sqlite3.connect(db_name)
- db_name 若存在,则直接连接;若不存在,则创建db_name.
2.设置row_factory,对查询到的数据,通过字段名获取列数据
- conn.row_factory =sqlite3.Row
3.获取游标,用于执行SQL语句
- conn.cursor()
4.CRUD操作
- cursor.execute(sql_str)
- cursor.executemany(sql_str)批量操作
5.获取单条记录:fetchone()
6.获取多条记录:fetechall()
7.提交操作:conn.commit()
8.关闭连接:conn.close()
代码演示:
import sqlite3 # 导入包
conn=sqlite3.connect ('./dataFile/sqliteDatabase.db') #连接数据库
conn.row_factory=sqlite3.Row #设置后按照字段名获取列数据
cur=conn.cursor()获取游标
cur.execute("select name from sqlite_master where type ='table';")
table_data = cur.fetchall()
for tabel_d in table_data:
print(table_d['name']
cur.execute("DROP TABLE IF EXISTS student")
#创建数据库并插入数据
cur.execute("CREATE TABLE student(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,SCORE FLOAT NOT NULL);")
cur.execute("INSERT INTO student VALUES(1,'Lucy',13,89)");
cur.execute("INSERT INTO student VALUES(2,'LiLy',15,90)")
cur.execute("INSERT INTO student VALUES(3,'Hanmei',17,100)")
students = (
(4,'LiLei',11,93),
(5,'Bobll',12,85),
(6,'July',13,98)
)
cur.executemany("INSERT INTO student VALUES(?, ?, ?, ?)",students);
cur.execute("UPDATE student set SCORE = 99 where ID=3") #将ID为3的学生成绩改为99
cur.execute("DELETE FROM student where ID=5") #删除ID为5的学生记录
cur.execute("SELECT * FROM student")
rows = cur.fetchall()
# 通过字段名"ID",'name','age','score'来查询列数据
for row in rows:
print("Id_%d: name=%s,age=%d,score=%d;"%(row["ID"],row["name"],row["age"],row["score"]))
conn.commit()
conn.close()
2).python之多表连接
- inner join …on 条件
- 生成两张表的交集,返回的记录为两张表的交集的记录数
- outer join …on 条件
- left join(A,B),返回表A的所有记录,另外表B中匹配的记录有值,没有匹配的记录返回null
- right join (A,B),返回表B的所有记录,另外表A中匹配的记录有值,没有匹配的记录返回null,
但是目前在sqlite3中不支持右连接,可考虑交换A、B表操作,使用left join实现右连接的功能。
cur.execute("SELECT stu_name,stu_class,tch_name,tch_class FROM student inner join teacher on stu_class=tch_class;")
图片@小象学院