Python数据库编程


1. 操作SQLite3数据库

  Python3.x版本开始,在标准库中已经内置了SQLlite3模块,它可以支持SQLite3数据库的访问和相关的数据库操作。

在需要操作SQLite3数据库数据时,只须在程序中导入SQLite3模块即可。操作SQLite3数据库基本流程如下:

(1)导入相关库或模块(SQLite3):import sqlite3

(2)使用 connect()连接数据库并获取数据库连接对象。连接函数connect()有如下两个常用参数:

    • database:表示要访问的数据库名。
    • timeout:表示访问数据的超时设定。

   它提供了以下方法:

    • .cursor() 方法来创建一个游标对象
    • .commit() 方法来处理事务提交
    • .rollback() 方法来处理事务回滚
    • .close() 方法来关闭一个数据库连接

(3)使用 cursor()获取游标对象。

(4)使用游标对象的方法( execute()、executemany()、fetchall() 等)来操作数据库,实现插入、修改和删除操作,并查询获取显示相关的记录。

(5)使用 close()关闭游标对象和数据库连接。数据库操作完成之后,必须及时调用其close()方法关闭数据库连接,目的是减轻数据库服务器的压力。

1.1 使用SQLite3创建表

  使用sqlite3模块的 connect() 方法来创建/打开数据库,需要指定数据库路径,不存在则创建一个新的数据库。

    con = sqlite3.connect('e:/sqllitedb/rider.db')

 【示例】使用SQLite3创建表

# 导入sqllite3模块
import sqlite3
# 硬盘上创建连接
con = sqlite3.connect('e:/sqlitedb/rider.db')
# 获取cursor游标对象
cur = con.cursor()
# 执行sql创建表
sql = 'create table t_person(pno INTEGER PRIMARY KEY  AUTOINCREMENT ,pname varchar(30) NOT NULL ,age INTEGER)'
try:
    cur.execute(sql)
except Exception as e:
    print('创建表失败:', e)
finally:  
    cur.close()        # 关闭游标
    con.close()        # 关闭连接

1.2 使用SQLite3插入数据

  调用游标对象的 execute() 执行插入的sql,使用 executemany() 执行多条sql语句,使用 executmany() 比循环使用 excute() 执行多条sql语句效率高。

注:插入数据成功需要用 游标对象的 commit()方法提交事务,失败要用 游标对象的 rollback()方法回滚事务。

【示例】使用SQLite3插入一条数据

# 导入sqllite3模块
import sqlite3
# 硬盘上创建连接
con = sqlite3.connect('e:/sqlitedb/rider.db')
# 获取cursor对象
cur = con.cursor()
# 插入的SQL
sql = 'insert into t_person(pname,age) values(?,?)'
try:
    cur.execute(sql,('张三',23))          # 执行插入的SQL  
    con.commit()           # 提交事务
    print('插入成功')
except Exception as e:
    print('插入失败:', e)
    con.rollback()        # 回滚事务
finally:
    cur.close()        # 关闭游标
    con.close()        # 关闭连接
 【示例】使用SQLite3插入多条数据

# 导入sqllite3模块
import sqlite3
# 硬盘上创建连接
con = sqlite3.connect('e:/sqlitedb/rider.db')
# 获取cursor对象
cur = con.cursor()
try:
    # 插入的SQL
    sql = 'insert into t_person(pname,age) values(?,?)'
    cur.executemany(sql, [('张三', 23), ('李四', 25), ('小红', 24), ('小李', 12)])    # 执行插入的SQL
    con.commit()           # 提交事务
    print('插入成功')
except Exception as e:
    print('插入失败:', e)
    con.rollback()        # 回滚事务
finally:
    cur.close()        # 关闭游标
    con.close()        # 关闭连接

1.3 使用SQLite3查询数据

  查询数据,游标对象提供了 fetchall() 和 fetchone() 方法 。fetchall()方法获取所有数据,返回一个列表。fetchone()方法获取其中一个结果,返回一个元组。

fetchall()查询所有数据

# 导入sqllite3模块
import sqlite3
# 硬盘上创建连接
con = sqlite3.connect('e:/sqlitedb/first.db')
# 获取cursor对象
cur = con.cursor()
sql = 'select * from t_person'
try:
    cur.execute(sql)        # 执行查询的sql
    # 获取所有数据
    person_all = cur.fetchall()
    print(person_all)
    for p in person_all:
        print(p)
except Exception as e:
    print('查询失败:', e)
finally:
    cur.close()        # 关闭游标
    con.close()        # 关闭连接

 Python数据库编程

 fetchone()查询一条数据

# 导入sqllite3模块
import sqlite3
# 硬盘上创建连接
con = sqlite3.connect('e:/sqlitedb/rider.db')
# 获取cursor对象
cur = con.cursor()
sql = 'select * from t_person'
try:
    cur.execute(sql)        # 执行查询的sql
    # 获取一条数据
    person = cur.fetchone()
    print(person)
except Exception as e:
    print('查询失败:', e)
finally:
    cur.close()        # 关闭游标
    con.close()        # 关闭连接

 Python数据库编程

1.4 更新数据和删除数据

注:更新和删除数据 成功需要用 游标对象的 commit()方法提交事务,失败要用 游标对象的 rollback()方法回滚事务。

修改数据

# 导入sqllite3模块
import sqlite3
# 硬盘上创建连接
con=sqlite3.connect('e:/sqlitedb/rider.db')
# 获取cursor对象
cur=con.cursor()
try:
    # 执行更新的SQL
    update_sql = 'update t_person set pname=? where pno=?'
    cur.execute(update_sql, ('小明', 1))
    con.commit()            # 提交事务
    print('修改成功')
except Exception as e:
    print('修改失败:', e)
    con.rollback()            # 回滚事务
finally:
    cur.close()        # 关闭游标
    con.close()        # 关闭连接

 Python数据库编程

 删除数据

# 导入sqllite3模块
import sqlite3
# 硬盘上创建连接
con=sqlite3.connect('e:/sqlitedb/rider.db')
# 获取cursor对象
cur=con.cursor()
delete_sql = 'delete from t_person where pno=?'
try:
    #执行删除的SQL
    cur.execute(delete_sql, (2,))
    con.commit()          # 提交事务
    print('删除成功')
except Exception as e:
    print('修改失败:', e)
    con.rollback()        # 回滚事务
finally:
    cur.close()        # 关闭游标
    con.close()        # 关闭连接

 

 

在上述实例代码中,首先定义查询所有数据、插入数据、修改数据、删除数据的方法。然后,定义主方法中依次建立连接,获取连接的cursor,通过cursor的execute()等方法来执行SQL语句,调用插入记录、更新记录、删除记录的方法。

2. 操作MySQL数据库

  PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2中则使用 mysqldb 。

搭建PyMySQL环境:

  在使用 PyMySQL 之前,需要安装 PyMySQL库 。使用命令安装最新版的 PyMySQL: pip install PyMySQL 

如果使用命令无法安装,需要下载PyMySQL-0.9.3-py2.py3-none-any.whl文件,进行安装:

进入python官网https://www.python.org –> 点击菜单PyPI  –> 输入pymsql,进行搜索 –> 点击PyMySQL0.9.3,点击左侧Download  files进行下载

–> windows+R打开doc窗口,进入PyMySQL-0.9.3-py2.py3-none-any.whl文件所在目录,执行命令安装:pip install PyMySQL-0.9.3-py2.py3-none-any.whl

2.1 创建数据库表

  在Python程序中,可以使用 execute() 在数据库中创建一个新表。

【例子】创建新表student

import pymysql
try:
    # 创建与数据库的连接
    db = pymysql.connect('localhost','root','root','testdb')
    # 创建游标对象cursor
    cursor = db.cursor()
    # 使用execute()方法执行sql,如果表存在则删除
    cursor.execute('drop table if EXISTS student')
    #创建表的sql
    sql='''
        create table student(
        sno int(8) primary key auto_increment,
        sname varchar(30) not null,
        sex varchar(5) ,
        age int(2),
        score float(3,1)
        )
    '''
    cursor.execute(sql)
except:
    print('创建表失败')
finally:
    #关闭数据库连接
    db.close()

2.2 数据库插入操作

  在Python程序中,可以使用SQL语句向数据库中插入新的数据信息。

跟SQLite3一样,调用游标对象的 execute()执行插入的sql,使用 executemany()执行多条sql语句,使用 executmany() 比循环使用 excute() 执行多条sql语句效率高。

【例子】向student表中插入数据信息

import pymysql
# 创建与数据库的连接
db = pymysql.connect('localhost','root','root','testdb')
# 创建游标对象cursor
cursor=db.cursor()
# 插入sql语句
sql='''
    insert into student(sname,sex,age,score) values(%s,%s,%s,%s)
'''
try:
    # 执行sql语句
    cursor.execute(sql,('李四','woman',25,99.6))
    # 提交事务
    db.commit()
    print('插入成功')
except Exception as e:
    print('插入失败:', e)
    # 如果出现异常,回滚
    db.rollback()
finally:
    # 关闭数据库连接
    db.close()

 【例子】向student表同时插入多条数据

import pymysql
# 创建与数据库的连接
db = pymysql.connect('localhost','root','root','testdb')
# 创建游标对象cursor
cursor=db.cursor()
# 插入sql语句
sql='''
    insert into student(sname,sex,age,score) values(%s,%s,%s,%s)
'''
args = [('王五','woman',22,98.6),('赵六','man',21,99.1)]
try:
    # 执行sql语句
    cursor.executemany(sql,args)
    # 提交事务
    db.commit()
    print('插入成功')
except Exception as e:
    print('插入失败:', e)
    # 如果出现异常,回滚
    db.rollback()  
finally:
    #关闭数据库连接
    db.close()

2.3 数据库查询操作

  Python查询Mysql使用 fetchone() 方法获取单条数据, 使用 fetchall() 方法获取多条数据。

  • fetchone():  该方法获取下一个查询结果集。结果集是一个对象
  • fetchall():  接收全部的返回结果行.
  • rowcount:  这是一个只读属性,并返回执行execute()方法后影响的行数。

【示例】查询学生 年龄大于等于23的所有学生信息

import pymysql
# 创建与数据库的连接
db = pymysql.connect('localhost','root','root','testdb')
# 创建游标对象cursor
cursor = db.cursor()
# 查询年龄大于等于23的所有学生信息
sql = 'select * from student where age>=23'
try:
    # 执行sql
    cursor.execute(sql)
    # 获取查询结果
    results = cursor.fetchall()
    for row in results:
        sno = row[0]
        sname = row[1]
        sex = row[2]
        age = row[3]
        score = row[4]
        # 输出
        print('sno:',sno,'sname:',sname,'sex:',sex,'age:',age,'score:',score)
except Exception as e:
    print('查询失败:', e)
finally:
    db.close()

2.4 数据库更新操作和删除操作

  在Python程序中,可以使用update语句更新数据库中数据信息;使用delete语句删除数据库中的数据信息。

【示例】更新数据库中的数据

import pymysql
# 创建与数据库的连接
db = pymysql.connect('localhost','root','root','testdb')
# 创建游标对象cursor
cursor = db.cursor()
# 将sno=5的学生成绩修改为99.5
sql = 'update student set score=%s where sno=%s'
try:
    # 执行sql
    cursor.execute(sql,(99.5,5))
    # 提交数据
    db.commit()
    print('修改成功')
except:
    print('修改失败')
    db.rollback()
finally:
    # 关闭连接
    db.close()

【示例】删除年龄小于22的学生

import pymysql
# 创建与数据库的连接
db = pymysql.connect('localhost','root','root','testdb')
# 创建游标对象cursor
cursor = db.cursor()
# 删除sql
sql = 'delete from student where age < 22'
try:
    # 执行sql语句
    cursor.execute(sql)
    # 提交事务
    db.commit()
    print('删除数据成功')
except:
    db.rollback()
    print('删除数据失败')
finally:
    # 关闭连接
    db.close()

3. 附:下载安装MySQL(界面版)

3.1 下载MySQL

  如果安装MySQL只是为了个人的学习和软件开发,安装免费的社区版即可。

(1) 首先进入MySQL的官网:https://www.mysql.com/,–> 然后点击DOWNLOADS导航栏,会默认进入到MySQL的Enterprise(企业版)下载页面,还需要点击Community(社区版)切换到社区版的下载页面, –> 最后点击MySQL Community Server下边的DOWNLOAD按钮即可进入MySQL数据库的下载页面。

(2) 进入MySQL的数据库下载界面后,首先在“Select Operating System”下拉菜单中选择“Microsoft Windows”平台,然后进入MySQL Installer MSI下载页面,如下图所示。

Python数据库编程   Python数据库编程

(3) 在MSI下载页面,选择正确的文件下载,此时MySQL官网会建议你注册或者登陆账号然后下载,可以选择“No thanks, just start my download.”直接下载。

Python数据库编程

3.2 安装MySQL

根据下载路径找到下载好的MySQL安装程序(mysql-installer-community-8.0.12.0.msi),具体步骤如下所示。

(1)双击安装程序mysql-installer-community-8.0.12.0.msi,弹出MySQL许可协议界面- -> 选择“I accept the license terms”后,点击“Next”进入安装类型选择界面 –> 选择自定义安装类型“Custom”(此类型可以根据用户自己的需求选择安装需要的产品),然后单击“Next”进入选择安装版本界面。

(2)在选择安装版本界面:展开第一个节点“MySQL Servers”,找到并点击“MySQL Server 8.0.12-X64”,点击绿色的箭头,将选中的产品添加到右边的待安装列表框中,再展开待安装列表中的MySQL Server 8.0.12-X64节点,取消“Development Components”选项前边的“√”,然后点击“Next”进入安装列表界面。

Python数据库编程  Python数据库编程

(3)点击安装列表界面的“Execute”按钮后,要安装的产品右边会显示一个进度百分比,安装完成之后会前边会出现绿色个的“√”。之后继续点击“Next”按钮即可。

Python数据库编程  Python数据库编程

完成以上3个步骤后,MySQL就安装成功了,剩下的就是对其进行配置。

3.3 配置MySQL

安装完成后,还需要设置MySQL的各项参数才能正常使用。仍然使用图形化界面对其进行配置,具体步骤如下:

1)  直接点击下图中的“Next”按钮,直接进入参数配置页面中的“Type and NetWorking”界面。

Python数据库编程

2) 进入“Type and Networking”界面后,会看到两个选项“Standalone MySQL Server / Classic MySQL Replication”和“InnoDB Cluster Sandbox Test Setup(for testing only)”。

如果要运行独立的MySQL服务器可以选择前者,以便稍后配置经典的MySQL复制,使用该选项,用户可以手动配置复制设置,并在需要时提供自己的高可用性解决方案。

而后者是InnoDB集群沙箱测试设置,仅用于测试。

我们要选择的是“Standalone MySQL Server / Classic MySQL Replication”选项,然后点击“Next”按钮即可,如下图所示。

3) 服务器配置类型“Config Type”选择“Development machine”,不同的选择将决定系统为MySQL服务器实例分配资源的大小,“Development machine”占用的内存是最少的;

 连接方式保持默认的TCP/IP,端口号也保持默认的3306即可;点击“Next”按钮。

  Python数据库编程  Python数据库编程

4) 接下来就是设置MySQL数据库Root账户密码,需要输入两遍。这个密码必须记住,后边会用到。此处我们将密码设置成“[email protected]”,之后点击“Next”按钮,如下图所示。

Python数据库编程  Python数据库编程

在配置Windows服务时,需要以下几部操作:

(1)勾选“Configure MySQL Server as a Windows Service”选项,将MySQL服务器配置为Windows服务;取消“Start the MySQL Server at System Startup”选项前边的“√”(该选项是设置是否开机自启动MySQL服务,在此我们选择开机不启动);勾选“Standard System Account”选项,该选项是标准系统账户,推荐使用该账户;点击“Next”按钮。

(2)接下来就是准备执行上述一系列配置的时候了,直接点击“Execute”按钮。等到所有的配置完成之后,会出现如下图所示的界面,点击“Finish”按钮,就会跳到配置成功界面,之后点击界面的“Next”按钮,再弹出的界面中点击“Finish”按钮即可完成配置。

Python数据库编程

4. 附:下载安装MySQL(界面版)

3.1 下载MySQL

官网:https://dev.mysql.com/  选择安装版下载:https://dev.mysql.com/downloads/mysql/ ,点击No thanks, just start my download.可立即下载

下载完后解压到本地目录,如 E:/mysql/mysql8023

初始化配置:

解压后,在目录E:/mysql/mysql8023/mysql-8.0.23-winx64下,

  • 新建一个Data文件夹,用于mysql数据库的数据存放,
  • 新建一个my.ini配置文件(新建文本,重命名为my.ini),内容如下:

  E:/mysql/mysql8023/mysql-8.0.23-winx64/my.ini 文件:

[mysqld]
# 设置3306端口
port = 3306

# 设置mysql的安装目录
basedir = E://mysql//mysql8023//mysql-8.0.23-winx64

# 设置mysql数据库的数据存放目录
datadir = E://mysql//mysql8023//mysql-8.0.23-winx64//Data

# 允许最大连接数
max_connections = 200

# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors = 10

# 服务端使用的字符集默认为UTF8
character-set-server = utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB

# 默认使用“mysql_native_password”插件认证
default_authentication_plugin = mysql_native_password

[mysql]
# 设置mysql客户端默认字符集
default-character-set = utf8

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port = 3306
default-character-set = utf8

 接下来初始化数据,找到cmd,右键以管理员权限打开cmd:

cd到bin目录: cd /d E:/mysql/mysql8023/mysql-8.0.23-winx64/bin

执行命令(初始化数据库):mysqld –initialize –console

安装mySQL或执行以上命令时报错找不到vcruntime140_1.dll的解决方案:

缺少了动态链接库(.ddl文件), 跟mysql无关, 下载一份即可。下载地址:https://pan.baidu.com/s/1QriqbRAA4l_40Kc0-007jQ 提取码:uyp7

https://cn.dll-files.com/download/6fe223ce568d919f80bea233738d0628/vcruntime140_1.dll.html?c=RE5hVFFwZFVkVk16MlY3dW43NDkyQT09

64位,解压后将vcruntime140_1.dll复制到C:/Windows/System32,而32位版本的需要复制到C:/Windows/SysWOW64下

执行完成后,会输出root用户的初始密码,如:

2021-03-14T10:27:59.584362Z 6 [Note] [MY-010454] [Server] A temporary password is generated for [email protected]: YQDfWiiKU0;Z

YQDfWiiKU0;Z就是初始密码,后续登录需要用到,也可以在登陆后修改密码。

注:要是关掉了或没记住,可删掉初始化的datadir目录(即建的Data文件夹),再执行一遍初始化命令mysqld –initialize –console,又会重新生成。

安装&启动mysql服务

  接着在bin目录下执行安装命令:mysqld install 或 mysqld –install mysql

  再执行启动命令:net start mysql

  通过命令net stop mysql 可停止服务。

登录MySQL

当 MySQL 服务已经运行时, 我们可以通过 MySQL 自带的客户端工具登录到 MySQL 数据库中, 首先打开命令提示符, 输入以下格式的命名:

  mysql -h 主机名 -u 用户名 -p

  • -h: 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或0.0.1)该参数可以省略;
  • -u: 登录的用户名;
  • -p: 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

登录本机的MySQL,只需输入:mysql –u root –p 按回车后提示输入密码

若密码存在, 输入密码登录, 不存在则直接按回车登录。

登录成功后你将会看到 Welcome to the MySQL monitor… 的提示语,然后命令提示符会一直以 mysq> 加一个闪烁的光标等待命令的输入, 输入 exit 或 quit 退出登录。

修改密码(注意命令结尾一定要有;):

  在MySQL安装目录bin目录下执行命令:mysql –u root –p 会提示输入密码,

  输入前面安装时的密码YQDfWiiKU0;Z 填入即可登录成功,进入mysql交互模式。

  在MySQL中执行命令:ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

 

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/281505.html

(0)
上一篇 2022年8月22日
下一篇 2022年8月22日

相关推荐

发表回复

登录后才能评论