PyMySQL
is a library used to connect to the MySQL
server in the Python3.x
version,#mysqldb
is used in ##Python2.
PyMySql follows the specifications of
Python Database API v2.0 and includes the
pure-Python MySQL client library.
$ pip install pymysql
PyMySQL to connect to the
MySQL database, Please ensure that the
MySQL database installation and configuration is complete. For details on how to install and configure the
MySQL database, please refer to MySQL installation and MySQL management.
import pymysql # 数据库服务器名 HOSTNAME = 'node05' # 数据库用户名 USER = 'root' # 数据库名 DATABASE = 'cayman' # 数据库密码 PASSWORD = 'Love88me' # 打开数据库连接 conn = pymysql.connect(HOSTNAME, USER, PASSWORD, DATABASE) # 使用cursor()方法创建一个游标对象 cursor = conn.cursor() # 使用execute()方法执行SQL查询语句 cursor.execute("select VERSION()") # 使用fetchone()查询单条数据 data = cursor.fetchone() print(f"Database Version: {data}") # 关闭数据库连接 conn.close()
import pymysql # 设置数据库配置项 HOSTNAME = 'node05' USERNAME = 'root' PASSWORD = 'Love88me' DATABASE = 'cayman' # 打开数据库连接 db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE) # 使用cursor对象创建一个流标对象 cursor = db.cursor() # 使用execute()方法执行SQL, 如果表存在则删除 cursor.execute("DROP TABLE IF EXISTS employee") # 使用预处理语句创建表 sql = """ CREATE TABLE employee( id bigint primary key auto_increment, user_name varchar(50) not null, age int, sex char(1), income float ) """ # 执行sql语句 cursor.execute(sql) # 关闭数据库连接 db.close()
import pymysql # 设置数据库配置项 HOSTNAME = 'node05' USERNAME = 'root' PASSWORD = 'Love88me' DATABASE = 'cayman' # 打开数据库连接 db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE) # 使用cursor对象创建一个流标对象 cursor = db.cursor() # SQL语句 sql = """ insert into employee(user_name, age, sex, income) values ('风清扬', 64, '男', 22000); """ try: # 执行sql语句 cursor.execute(sql) # 提交 db.commit() except: # 如果发生错误就回滚 db.rollback() # 关闭数据库连接 db.close()
import pymysql # 设置数据库配置项 HOSTNAME = 'node05' USERNAME = 'root' PASSWORD = 'Love88me' DATABASE = 'cayman' # 打开数据库连接 db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE) # 使用cursor对象创建一个流标对象 cursor = db.cursor() # SQL语句 sql = " insert into employee(user_name, age, sex, income) values (%s, %s, %s, %s)" data = ( ('风清扬', 64, '男', 22000), ('令狐冲', 22, '男', 14000), ('任盈盈', 20, '男', 10000), ('东方不败', 32, '男', 18000), ('任我行', 56, '男', 17000), ('段誉', 33, '男', 19000), ('王语嫣', 26, '女', 9000), ('木婉清', 23, '女', 6000), ('乔峰', 38, '男', 23000), ('阿朱', 24, '女', 5000), ('阿紫', 22, '女', 5500), ('虚竹', 35, '男', 11000), ('梦姑', 25, '女', 6500), ('梅超风', 41, '女', 15000), ('陈玄风', 44, '男', 12000), ('杨过', 28, '男', 24000), ('小龙女', 38, '女', 15000), ('鸠摩智', 44, '男', 16000) ) try: # 执行sql语句 cursor.executemany(sql, data) # 提交 db.commit() except: # 如果发生错误就回滚 db.rollback() # 关闭数据库连接 db.close()
PythonQuery
MySQLUse
fetchone() to obtain a single piece of data, Use the
fetchall() method to obtain multiple pieces of data.
employeeAll data in the table
income(salary) is greater than 20000
# 1.查询employee表中工资大于20000的员工信息 import pymysql # 设置数据库配置项 HOSTNAME = 'node05' USERNAME = 'root' PASSWORD = 'Love88me' DATABASE = 'cayman' # 打开数据库连接 db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE) # 使用cursor对象创建一个流标对象 cursor = db.cursor() # 查询语句 sql = "select * from employee where income >'%d' "%(20000) try: # 执行sql语句 cursor.execute(sql) # 获取所有满足条件的列表 ret = cursor.fetchall() # 遍历打印结果 for row in ret: user_name = row[1] age = row[2] sex = row[3] income = row[4] print(f"员工: {user_name},年龄: {age}, 性别: {sex}, 工资: {income}") except: print("无满足条件的数据或查询出错!!") # 关闭数据库连接 db.close()4.5. Database update operation
import pymysql # 设置数据库配置项 HOSTNAME = 'node05' USERNAME = 'root' PASSWORD = 'Love88me' DATABASE = 'cayman' # 打开数据库连接 db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE) # 使用cursor对象创建一个流标对象 cursor = db.cursor() # 更新语句 sql = "update employee set income=income+income*0.1 where sex='%c'"%('女') try: # 执行SQL语句 cursor.execute(sql) # 提交 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭数据库 db.close()
import pymysql # 设置数据库连接信息 HOSTNAME = 'node05' USERNAME = 'root' PASSWORD = 'Love88me' DATABASE = 'cayman' # 打开数据库连接 db = pymysql.connect(HOSTNAME, USERNAME, PASSWORD, DATABASE) # 使用cursor()方法获取游标 cursor = db.cursor() # 构建删除数据SQL语句 sql = "delete from employee where user_name = '%s'"%('鸠摩智') try: # 执行sql语句 cursor.execute(sql) # 提交 db.commit() except: # 发生异常时回滚 db.rollback() # 关闭数据库连接 db.close()
ACID properties.
Python DB API 2.0 transactions provide two methods
commit and
rollback. For database programming that supports transactions, when the stream mark is established, an invisible database transaction is automatically started.
The above is the detailed content of How to install and configure MySQL database. For more information, please follow other related articles on the PHP Chinese website!