python之数据库编程
python之数据库编程
sqlite
1.前期准备工作
-
导入模块：
import sqlite3 -
连接数据库
conn = sqlite3.connect("test.db") #test为数据库名称，若不存在此数据库，会自动创建 -
测试是否创建或连接数据库成功
print(conn) #打印结果为connection对象 -
pycharm端显示出数据库：
- 1.打开pycharm->右端database->点击+号
- 1.打开pycharm->右端database->点击+号
-
2.第二步：
-
3.第三步
-
4.选择pycharm工作环境下新创建的数据库，比如我的在这里
点击ok即可，注意：
若第三步出现这个，点击Download先进行下载。。。
2.创建数据库和表
import sqlite3
conn = sqlite3.connect("test.db")
#cur = conn.cursor()
sql = """
CREATE TABLE test(
id INTEGER PRIMARY KEY autoincrement,
name TEXT,
age INTEGER
)
"""
try:
#cur.execute(sql)
conn.execute(sql)
except Exception as e:
print(e)
finally:
#cur.close()
conn.close()
注意：
可以看到，此处并没有使用游标，而是直接conn.execute(sql)，值得说明的是，对sqlite来说（mysql却不是），增删改以及创建表都可以不用游标，但查询一定需要，往下看
3.插入数据
import sqlite3
conn = sqlite3.connect('test.db')
# cur = conn.cursor()
sql = """
insert into test(name, age) VALUES (%s,%s)
"""%("'王五'",22)
sql1 = """
insert into test(name, age) VALUES (?,?)
"""
try:
print("sql:"+sql)
print("sql1:" + sql1)
#conn.execute(sql)
#conn.execute(sql1, ('张三', 20))添加单条数据
conn.executemany(sql1,[('李四',18),('王五',28),('赵六',38)])
conn.commit()
print("插入成功")
except Exception as e:
print(e)
print("插入失败")
conn.rollback()
finally:
conn.close()
显示结果：
插入数据中使用了两种方法，见sql和sql1，分别使用%s和?占位符
以下我就不一一展示显示结果了。。。
4.修改数据
import sqlite3
conn = sqlite3.connect('test.db')
# cur = conn.cursor()
sql = """
update test set name = ? , age = ? where id = ?
"""
sql1 = """
update test set name = %s , age = %s where id = %s
"""%("'曹操'", 24, 11)
try:
#conn.execute(sql, ('曹操', 24, 4))
conn.execute(sql1)
print("成功")
conn.commit()
except Exception as e:
print(e)
print("失败")
conn.rollback()
finally:
conn.close()
修改数据中使用了两种方法，见sql和sql1，分别使用%s和?占位符
5.删除数据
import sqlite3
conn = sqlite3.connect('test.db')
#cur = conn.cursor()
sql = """
delete from test where name = ?
"""
sql1 = """
delete from test where name = %s
"""%("'曹操'",)
try:
conn.execute(sql1);
#conn.execute(sql,('曹操',));
#若执行conn.execute(sql,('曹操'));会报错Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.
#使用('曹操',)或[曹操]
conn.commit()
except Exception as e:
conn.rollback()
print(e)
finally:
conn.close()
6.查询数据
import sqlite3
conn = sqlite3.connect('test.db')
cur = conn.cursor()
sql = """
select * from test
"""
try:
cur.execute(sql);
# print(cur.fetchall())#查询所有数据
print(cur.fetchone()) # 查询第一条数据
# print(cur.fetchmany(3))#查询几条数据，从开头开始
except Exception as e:
print(e)
finally:
cur.close()
conn.close()
可以看到，查询数据必须要用游标，按条件进行查询可参照上面的占位符进行测试
7.模糊查询
import sqlite3
conn = sqlite3.connect('test.db')
cur = conn.cursor()
sql = "select * from test where name like '%%%s%%'"%"王"
#sql1 = "select * from test where name like ?"
try:
print(sql)
cur.execute(sql);
#cur.execute(sql1, ("%王%",));
print(cur.fetchall()) # 查询所有数据
# print(cur.fetchone()) # 查询第一条数据
# print(cur.fetchmany(3))#查询几条数据，从开头开始
except Exception as e:
print(e)
finally:
cur.close()
conn.close()
模糊查询中使用了两种方法，见sql和sql1，分别使用%s和?占位符
注意：%s占位符模糊查询时，使用%%---%%进行转义
mysql
1.前期准备工作
-
导入模块：
import pymysql -
连接数据库
conn = pymysql.connect(user='root',password='000000',host='localhost', port=3306,database='python_test') #mysql数据库不会自动创建，需要自己建立 -
测试是否连接数据库成功
print(conn) #打印结果为connection对象 -
pycharm端显示出数据库：
与sqlite操作一样，此时建立的是mysql
2.创建数据库和表
import pymysql
conn = pymysql.connect(user='root',password='000000',host='localhost',
port=3306,database='python_test')
cur = conn.cursor()
sql = """
create table student(
id integer primary key auto_increment,
sno char(20) not null,
name char(20),
score float
)
"""
try:
cur.execute(sql)
print("建表成功")
except Exception as e:
print(e)
print("建表失败")
finally:
cur.close()
conn.close()
注意：
可以看到，与sqlite不同，mysql的操作都需要游标参与
3.插入数据
import pymysql
conn = pymysql.connect(user='root', password='000000', host='localhost', port=3306, database='python_test')
cur = conn.cursor()
sql = """
insert into student(sno, name, score)
values
(%s,%s,%s)
"""
try:
#cur.execute(sql, ('7777', 'ff', 90)) #插入一条
cur.executemany(sql,[('3333','c',3),('4444','d',4)]) #插入多条
conn.commit()
print("插入成功")
except Exception as e:
print(e)
conn.rollback()
print("插入失败")
finally:
cur.close()
conn.close()
显示结果：
mysql中使用%s做占位符
4.修改数据
import pymysql
conn = pymysql.connect(user='root', password='000000', host='localhost', port=3306, database='python_test')
cur = conn.cursor()
sql = """
update student set name = %s where id = %s
"""
try:
cur.execute(sql,('王五',5))
#cur.executemany(sql,[('3333','c',3),('4444','d',4)])
conn.commit()
print("修改成功")
except Exception as e:
print(e)
conn.rollback()
print("修改失败")
finally:
cur.close()
conn.close()
5.删除数据
import pymysql
conn = pymysql.connect(user='root', password='000000', host='localhost', port=3306, database='python_test')
cur = conn.cursor()
sql = """
delete from student where name = %s
"""
try:
cur.execute(sql,'d')
#cur.executemany(sql,[('3333','c',3),('4444','d',4)])
conn.commit()
print("删除成功")
except Exception as e:
print(e)
conn.rollback()
print("删除失败")
finally:
cur.close()
conn.close()
6.查询数据
import pymysql
conn = pymysql.connect(user='root', password='000000', host='localhost', port=3306, database='python_test')
cur = conn.cursor()
sql = """
select * from student
"""
try:
cur.execute(sql)
#print(cur.fetchone())
#print(cur.fetchmany(3))
print(cur.fetchall())
except Exception as e:
print(e)
finally:
cur.close()
conn.close()
7.模糊查询
import pymysql
conn = pymysql.connect(user='root', password='000000', host='localhost', port=3306, database='python_test')
cur = conn.cursor()
sql = """
select * from student where name like %s
"""
sql1 = "select * from student where name like '%%%s%%'"%'王'
try:
print(sql1)
cur.execute(sql1)
#print(cur.fetchone())
#print(cur.fetchmany(3))
print(cur.fetchall())
except Exception as e:
print(e)
finally:
cur.close()
conn.close()
