这是我从头开始学习sql/mysql的学习笔记,后续还会更新。
记录自己的技术成长,也希望和大家分享交流,欢迎关注~
本笔记为coursera网站课程《Databases and SQL for Data Science with Python》的学习笔记
文章目录
In this module you will learn the basic concepts related to using Python to connect to databases. In a Jupyter Notebook, you will create tables, load data, query data using SQL, and analyze data using Python
完成本模块后, 您将能够解释基本概念 与使用Python连接数据库有关。 然后,您将创建表格, 使用pymysql中的SQL语句加载数据和查询数据, 最后,使用Python分析数据。 您将能够解释相关的基本概念 将Python应用程序连接到数据库。 描述SQL API并列出一些 流行的基于SQL的DBMS系统使用的专有API
文章目录
学习目标
- 描述与使用Python访问数据库有关的概念
- 演示如何使用SQL Magic从Python执行简化的数据库访问
- 演示编写SQL查询并从Python检索结果集
- 演示如何从Jupyter笔记本连接到数据库
- 演示如何从Python创建表和插入数据
如何使用Python访问数据库
Python的一些好处
1.Python生态系统非常丰富,并提供了易于使用的数据科学工具。 一些最受欢迎的软件包是NumPy, 熊猫,matplotlib和SciPy。
2.Python易于学习,语法简单。
3.由于其开源性质, Python已移植到许多平台。 您所有的python程序都可以在以下任何一种上运行 这些平台,根本不需要任何更改。 如果您谨慎并避免使用任何与系统相关的功能, Python支持关系数据库系统。
编写Python代码以访问数据库 Python数据库API的存在使操作变得更加容易。 通常称为DB API, 并且可以轻松获得与Python相关的详细文档。
Jupyter笔记本
Jupyter笔记本是一个开源Web应用程序, 允许您创建和共享包含实时代码的文档, 等式,可视化和叙述文本。 以下是使用Jupyter笔记本电脑的一些优点。
1.笔记本支持40多种编程语言,包括Python,R
2.笔记本可以通过电子邮件与他人共享,Dropbox,GitHub和Jupyter笔记本查看器。
3.您的代码可以产生丰富的交互式输出HTML, 图片,视频,LaTex和自定义类型。 您可以利用Python,R, 和Scala,并通过熊猫探索相同的数据, scikit-learn,ggplot2和TensorFlow。
典型用户访问的方式 数据库,使用在Jupyter笔记本上编写的Python代码, 基于网络的编辑器。
Python程序与DBMS有一种通信机制
Python代码使用API调用连接到数据库。 我们将解释 SQL API和Python DB API。
要将SQL语句传递给DBMS, 应用程序调用API中的函数, 它调用其他函数来检索 从DBMS查询结果和状态信息。
我们将在Jupyter笔记本上调用API,使用python语言,对数据库进行操作
由于无法连入课程网站的实验环境,所以以下步骤直接在pycharm内进行
参考文献:https://blog.csdn.net/weixin_42213622/article/details/86523400
python连接mysql数据库
1.操作步骤
pyton连接数据库需要先安装pymysql模块:pip install pymysql
安装完成后导入pymysql模块:import pymysql
python连接数据库主要分五个步骤:
step1:连接数据库
step2:创建游标对象。调用连接对象的cursor()方法返回游标对象,然后调用游标对象的execute()方法执行查询语句,查询数据库
step3:对数据库进行增删改查。调用连接对象的execute()方法,执行SQL语句,进行增删改的操作,如进行了增添或者修改数据的操作,需调用commit()方法提交修改才能生效;execute()方法也可用于执行DDL语句进行创建表的操作
step4:关闭游标
step5:关闭连接
2.游标对象
调用连接对象的cursor()方法可以得到一个游标对象,那么游标到底是什么呢?其实可以把游标理解为一个指针,如下图:
图中的指针就是游标cursor,假设右边的表就是查询到的结果,那么可以调用游标对象的fetchone()方法移动游标指针,每调用一次fetchone()方法就可以将游标指针向下移动一行,第一次调用fetchone()方法时,将游标从默认位置移动到第一行。
Cursor游标对象的几个常用方法:
execute() 执行sql语句,通常执行查询语句
fetchone() 将游标指针向下移动一行,并返回当前行的数据
fetchall() 从结果中取出所有结果,返回所有结果的列表
close() 关闭游标
查询返回的一行数据是一个元组,如上面代码中,print(row),其中row是一个元组,通过row[0]、row[1]等访问每一列数据,索引0对应上面代码中的id,索引1对应name,2对应age,以此类推
python必须有一个游标对象, 用来给数据库发送sql语句, 并执行的
3.具体代码
import pymysql
# 1. 连接数据库,
conn = pymysql.connect(
host='localhost',
user='root',
password='****',
db='demo',
charset='utf8',
# autocommit=True, # 如果插入数据,, 是否自动提交? 和conn.commit()功能一致。
)
# ****python, 必须有一个游标对象, 用来给数据库发送sql语句, 并执行的.
# 2. 创建游标对象,
cur = conn.cursor()
# 3. 对于数据库进行增删改查
#
# 1). ************************创建数据表**********************************
try:
create_sqli = "create table hello (id int, name varchar(30));"
cur.execute(create_sqli)
#注意执行的sql语句,必须用双引号输入
except Exception as e:
print("创建数据表失败:", e)
else:
print("创建数据表成功;")
# 2). *********************插入数据****************************
try:
insert_sqli = "insert into hello values(2, 'fensi');"
cur.execute(insert_sqli)
except Exception as e:
print("插入数据失败:", e)
else:
# 如果是插入数据, 一定要提交数据, 不然数据库中找不到要插入的数据;
conn.commit()
print("插入数据成功;")
# 3). **************************数据库查询*****************************
sqli = "select * from customers;"
result = cur.execute(sqli) # 默认不返回查询结果集, 返回数据记录数。
print(result)
print(cur.fetchone()) # 1). 获取下一个查询结果集;
print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchmany(4)) # 2). 获取制定个数个查询结果集;
info = cur.fetchall() # 3). 获取所有的查询结果
print(info)
print(len(info))
print(cur.rowcount) # 4). 返回执行sql语句影响的行数
# # 3). *********************插入多条数据****************************
try:
info = [(i, "westos%s" %(i)) for i in range(100)]
# *********************第一种方式********************
# %s必须手动添加一个字符串, 否则就是一个变量名, 会报错.
insert_sqli = "insert into hello values(%d, '%s');"
for item in info:
print('insert语句:', insert_sqli %item)
cur.execute(insert_sqli %item)
except Exception as e:
print("插入多条数据失败:", e)
else:
# 如果是插入数据, 一定要提交数据, 不然数据库中找不到要插入的数据;
conn.commit()
print("插入多条数据成功;")
# 4). 移动游标指针
sqli = "select * from customers;"
result = cur.execute(sqli) # 默认不返回查询结果集, 返回数据记录数。
print(result)
print(cur.fetchmany(3))
print("正在移动指针到最开始......")
cur.scroll(0, 'absolute')
print(cur.fetchmany(3))
print("正在移动指针到倒数第2个......")
print(cur.fetchall()) # 移动到最后
cur.scroll(-2, mode='relative')
print(cur.fetchall())
# 4. 关闭游标
cur.close()
# 5. 关闭连接
conn.close()
注意:
# 增添或者修改数据只会必须要提交才能生效
conn.commit()
另:对于SQLite数据库的简单代码
是一款轻型的数据库,是关系型数据库(RDBMS)管理系统,它包含在一个相对小的C库中。
一个简化版的代码:
# 导入模块
import sqlite3
# 连接数据库,返回连接对象
conn = sqlite3.connect("D:/my_test.db")
# 调用连接对象的execute()方法,执行SQL语句
# (此处执行的是DDL语句,创建一个叫students_info的表)
conn.execute("""create table if not exists students_info (
id integer primary key autoincrement,
name text,
age integer,
address text)""")
# 插入一条数据
conn.execute("insert into students_info (name,age,address) values ('Tom',18,'北京东路')")
# 增添或者修改数据只会必须要提交才能生效
conn.commit()
# 调用连接对象的cursor()方法返回游标对象
cursor = conn.cursor()
# 调用游标对象的execute()方法执行查询语句
cursor.execute("select * from students_info")
# 执行了查询语句后,查询的结果会保存到游标对象中,调用游标对象的方法可获取查询结果
# 此处调用fetchall方法返回一个列表,列表中存放的是元组,
# 每一个元组就是数据表中的一行数据
result = cursor.fetchall()
#遍历所有结果,并打印
for row in result:
print(row)
#关闭
cursor.close()
conn.close()
4.操作运行结果截图:
1.创建数据表:
2.插入数据;
3.插入多条数据(第一种方法);
4.数据库查询;
5.移动游标指针
获取表的字段名和信息
首先导入所需要的模块
然后进入主程序部分:此部分用with语句编写(with语句实现的效果是: with语句执行结束, 如果成功, 则提交改变的数据, 如果不成功, 则回滚.)
import time
import pymysql
conn = pymysql.connect(
host='localhost',
user='root',
password='*****',
db='demo',
charset='utf8',
# autocommit=True, # 如果插入数据,, 是否自动提交? 和conn.commit()功能一致。
) #连接数据库
# ****** 判断是否连接?
print(conn.open) # True
# 2. 创建游标对象,
cur = conn.cursor()
# 3.对数据库查询
sqli = "select * from hello;"
result = cur.execute(sqli) # 默认不返回查询结果集, 返回数据记录数。
# 显示每列的详细信息
des = cur.description
print("表的描述:", des)
# 获取表头
print("表头:", ",".join([item[0] for item in des]))
# 4.关闭游标和连接
cur.close() #关闭游标,with语句内部
conn.close() #关闭指针,with语句外部
操作运行结果:
基于mysql数据库银行转账功能实现
银行转账系统考虑因素主要包括:
1). 判断两个银行卡号是否存在?
2). 判断source_id是否有足够的钱?
3). source_id扣钱
4). target_id加钱
代码如下:
import pymysql
class TransferMoney(object):
# 构造方法
def __init__(self, conn):
self.conn = conn
self.cur = conn.cursor()
def transfer(self, source_id, target_id, money):
if not self.check_account_avaialbe(source_id):
raise Exception("账户不存在")
if not self.check_account_avaialbe(target_id):
raise Exception("账户不存在")
if self.has_enough_money(source_id, money):
try:
self.reduce_money(source_id, money)
self.add_money(target_id, money)
except Exception as e:
print("转账失败:", e)
self.conn.rollback()
else:
self.conn.commit()
print("%s给%s转账%s金额成功" % (source_id, target_id, money))
def check_account_avaialbe(self, acc_id):
"""判断帐号是否存在, 传递的参数是银行卡号的id"""
select_sqli = "select * from bankData where id=%d;" % (acc_id)
print("execute sql:", select_sqli)
res_count = self.cur.execute(select_sqli)
if res_count == 1:
return True
else:
# raise Exception("账户%s不存在" %(acc_id))
return False
def has_enough_money(self, acc_id, money):
"""判断acc_id账户上金额> money"""
# 查找acc_id存储金额?
select_sqli = "select money from bankData where id=%d;" % (acc_id)
print("execute sql:", select_sqli)
self.cur.execute(select_sqli) # ((1, 500), )
# 获取查询到的金额钱数;
acc_money = self.cur.fetchone()[0]
# 判断
if acc_money >= money:
return True
else:
return False
def add_money(self, acc_id, money):
update_sqli = "update bankData set money=money+%d where id=%d" % (money, acc_id)
print("add money:", update_sqli)
self.cur.execute(update_sqli)
def reduce_money(self, acc_id, money):
update_sqli = "update bankData set money=money-%d where id=%d" % (money, acc_id)
print("reduce money:", update_sqli)
self.cur.execute(update_sqli)
# 析构方法
def __del__(self):
self.cur.close()
self.conn.close()
if __name__ == '__main__':
# 1. 连接数据库,
conn = pymysql.connect(
host='localhost',
user='root',
password='******',
db='demo',
charset='utf8',
autocommit=True, # 如果插入数据,, 是否自动提交? 和conn.commit()功能一致。
)
trans = TransferMoney(conn)
trans.transfer(13997, 13998, 200) # 账户13997向13998转账200
先建立对应的表:
测试时,首先在数据库中构造数据表bankData,在数据表中添加用户信息(包括用户帐号和账户内金额)如下:
运行代码后数据库内金额发生变化,由账户13997向账户13998转账200元:
(需要提交事务)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5GcwmhB4-1628408165590)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210427192947760.png)]
总结
- 我们学习了如何使用python调用mysql数据库,其中我们需要使用一些api,这里我们使用pymysql模块
- 我们还学了Jupyter笔记本的使用
- 我们直到使用python操作数据库需要五步:import对应的包;连接数据库,创建库表,创建游标;使用对应函数execute对数据库进行增删改查;提交增加或修改后的数据库,并关闭游标和连接。
- 我们学习了游标,游标被用来依次访问数据库的内容
- 我们还应用了一个使用python的数据库应用实例,基于mysql的银行账户转移资金应用