python2和python3连接MySQL数据库

74 阅读 0 评论 49 点赞

我是靠谱客的博主飞快帆布鞋，这篇文章主要介绍python2和python3连接MySQL数据库，现在分享给大家，希望可以做个参考。

一、代码实现

python2使用MySQLdb模块

python3使用pymysql模块

代码用的是python2，去掉注释可换成python3

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#-*- encoding: utf-8 -*-
'''
MysqlUtil.py
Created on 2020/8/27 20:33
Copyright (c) 2020/8/27, Google Copy right
@author: com
'''

import MySQLdb
# import pymysql
 
try:
    db = MySQLdb.connect(host="localhost", user="root", passwd="123456", db="test", charset="utf8")
    # db = pymysql.connect(host="localhost", user="root", passwd="123456", db="test", charset="utf8")
    cursor = db.cursor()
    cursor.execute("SELECT VERSION()")
    data = cursor.fetchone()
    print("MySQL Version : %s" % data)
    cursor.execute("SELECT * FROM user")
    result = cursor.fetchall()
    for row in result:
        print("%d t %s t %d" % row)
    db.commit()
except:
    db.rollback()
finally:
    db.close()

二、执行结果

三、测试数据

复制代码

/*
 Navicat Premium Data Transfer

Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50722
 Source Host           : localhost:3306
 Source Schema         : test

Target Server Type    : MySQL
 Target Server Version : 50722
 File Encoding         : 65001

Date: 22/09/2020 16:44:52
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '新中国', 70);
INSERT INTO `user` VALUES (2, '吴京', 50);
INSERT INTO `user` VALUES (3, '李小龙', 32);
INSERT INTO `user` VALUES (4, '科比', 43);
INSERT INTO `user` VALUES (5, '胡歌', 40);
INSERT INTO `user` VALUES (6, '汪大东', 30);
INSERT INTO `user` VALUES (7, '汪大东', 30);

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50722
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50722
 File Encoding         : 65001

 Date: 22/09/2020 16:44:52
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '新中国', 70);
INSERT INTO `user` VALUES (2, '吴京', 50);
INSERT INTO `user` VALUES (3, '李小龙', 32);
INSERT INTO `user` VALUES (4, '科比', 43);
INSERT INTO `user` VALUES (5, '胡歌', 40);
INSERT INTO `user` VALUES (6, '汪大东', 30);
INSERT INTO `user` VALUES (7, '汪大东', 30);

SET FOREIGN_KEY_CHECKS = 1;