Python 3.x database connection example (pymysql method)

Since the MySQLdb module does not yet support Python3.x, if Python3.x wants to connect to MySQL, you need to install the pymysql module.

The pymysql module can be installed via pip. But if you are using pycharm IDE, you can use project python to install third-party modules.

[File] >> [settings] >> [Project: python] >> [Project Interpreter] >> [Install button]

Since Python unifies the database connection interface, pymysql and MySQLdb are similar in usage:

pymysql.Connect() parameter description

• host(str): MySQL server address

• port(int): MySQL server port number

• user (str): Username

• ##passwd(str): Password

• db(str): Database name

• charset(str): Connection encoding

Methods supported by connection object

• cursor() Use this connection to create and return a cursor

• commit() Commit the current transaction

• rollback() Rollback the current transaction

• close()                                                                                                                                                                         # Execute a database query command

fetchone() Get the next row of the result set

fetchmany(size) Get the next few rows of the result set

• fetchall() Get all the rows in the result set

• rowcount() Return the number of data or the number of affected rows

• close()                                                                                                    Close the cursor object

• ================== MySQL======== ============
• First, before connecting to the database, create a transaction table to facilitate testing the functions of pymysql:

DROP TABLE IF EXISTS `trade`;

CREATE TABLE `trade` (
 `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(6) NOT NULL COMMENT '用户真实姓名',
 `account` varchar(11) NOT NULL COMMENT '银行储蓄账号',
 `saving` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '账户储蓄金额',
 `expend` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '账户支出总计',
 `income` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '账户收入总计',
 PRIMARY KEY (`id`),
 UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `trade` VALUES (1,'乔布斯','18012345678',0.00,0.00,0.00);

• ==================Python====================

Use Python script implements addition, deletion, modification, query and transaction processing. The source code is as follows:

import pymysql.cursors

# 连接数据库
connect = pymysql.Connect(
  host='localhost',
  port=3310,
  user='woider',
  passwd='3243',
  db='python',
  charset='utf8'
)

# 获取游标
cursor = connect.cursor()

# 插入数据
sql = "INSERT INTO trade (name, account, saving) VALUES ( '%s', '%s', %.2f )"
data = ('雷军', '13512345678', 10000)
cursor.execute(sql % data)
connect.commit()
print('成功插入', cursor.rowcount, '条数据')

# 修改数据
sql = "UPDATE trade SET saving = %.2f WHERE account = '%s' "
data = (8888, '13512345678')
cursor.execute(sql % data)
connect.commit()
print('成功修改', cursor.rowcount, '条数据')

# 查询数据
sql = "SELECT name,saving FROM trade WHERE account = '%s' "
data = ('13512345678',)
cursor.execute(sql % data)
for row in cursor.fetchall():
  print("Name:%s\tSaving:%.2f" % row)
print('共查找出', cursor.rowcount, '条数据')

# 删除数据
sql = "DELETE FROM trade WHERE account = '%s' LIMIT %d"
data = ('13512345678', 1)
cursor.execute(sql % data)
connect.commit()
print('成功删除', cursor.rowcount, '条数据')

# 事务处理
sql_1 = "UPDATE trade SET saving = saving + 1000 WHERE account = '18012345678' "
sql_2 = "UPDATE trade SET expend = expend + 1000 WHERE account = '18012345678' "
sql_3 = "UPDATE trade SET income = income + 2000 WHERE account = '18012345678' "

try:
  cursor.execute(sql_1) # 储蓄增加1000
  cursor.execute(sql_2) # 支出增加1000
  cursor.execute(sql_3) # 收入增加2000
except Exception as e:
  connect.rollback() # 事务回滚
  print('事务处理失败', e)
else:
  connect.commit() # 事务提交
  print('事务处理成功', cursor.rowcount)

# 关闭连接
cursor.close()
connect.close()

================== Test results===================

The above is the entire content of this article, I hope it will be helpful to everyone’s learning Help, and I hope everyone will support the PHP Chinese website.

For more Python 3.x database connection examples (pymysql method) related articles, please pay attention to the PHP Chinese website!