Python使用pymysql往数据库插入数据和查询数据

Python使用pymysql往数据库插入数据和查询数据

• 第一步：导入import pymysql连接数据库

	#连接数据库
    try:
        db = pymysql.connect(host='127.0.0.1', port=3306,
                             user='root', passwd='12345678', db='videodb', charset='utf8mb4')
        print('连接数据库成功')
    except Exception as e:
        print(e)
    cursor = db.cursor()

• 第二步：执行sql语句

执行插入 和更新

# 插入一条数据
    line = "我总是四处走，然后在某个角落，遇见你。不是偶遇，我们只是邂逅了时光，横陈着的美丽。"

    # 字符串需要处理一下
    inputLine = pymysql.escape_string(line)
    # datetime的插入处理
    create_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    # # datetime的插入处理2
    # create_time = time.strftime('%Y-%m-%d %X', time.localtime(time.time()))

    # 构造sql语句
    sql = "insert into words (word,add_date) values ('%s','%s');" % (
        inputLine, create_time)
    #尝试写入数据库
    try:
        db.begin()
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        db.rollback()
        print(e)

注：插入和更新cursor.execute(sql)一定要使用db.commit() 数据才能更改

查询

	# 查询数据库 inputline 
    select_sql = "SELECT * FROM words where word = '%s'" % inputLine
    cursor.execute(select_sql)
    info = cursor.fetchall()[0]
    print(info)

注：查询出来的数据为元组（Tuple）类型

• 最后：关闭数据库

db.close()
cursor.close()

插入文本并查询实例

words表

/*
 Navicat Premium Data Transfer

 Source Server         : mac local
 Source Server Type    : MySQL
 Source Server Version : 80021
 Source Host           : localhost:3306
 Source Schema         : videodb

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

 Date: 16/10/2020 14:15:40
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for words
-- ----------------------------
DROP TABLE IF EXISTS `words`;
CREATE TABLE `words` (
  `id` int NOT NULL AUTO_INCREMENT,
  `word` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `add_date` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;

python 代码

# 获取文本存入数据库

import requests
import pymysql
import re
import sys
import os
import datetime


# 运行的方法
def run():
    #连接数据库
    try:
        db = pymysql.connect(host='127.0.0.1', port=3306,
                             user='root', passwd='12345678', db='videodb', charset='utf8mb4')
        print('连接数据库成功')
    except Exception as e:
        print(e)
    cursor = db.cursor()


    # 插入一条数据
    line = "我总是四处走，然后在某个角落，遇见你。不是偶遇，我们只是邂逅了时光，横陈着的美丽。112"

    # 字符串需要处理一下
    inputLine = pymysql.escape_string(line)
    # datetime的插入处理
    create_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    # # datetime的插入处理2
    # create_time = time.strftime('%Y-%m-%d %X', time.localtime(time.time()))

    
    # 查询数据库 inputline的条数
    select_sql = "SELECT count(*) FROM words where word = '%s'" % inputLine
    cursor.execute(select_sql)
    count = cursor.fetchall()[0][0]
    print(count)
    if (count > 0):
        print("已经存在！")
    else:
        print("不存在，开始插入数据")
        # 构造sql语句
        sql = "insert into words (word,add_date) values ('%s','%s');" % (inputLine, create_time)
        #尝试写入数据库
        try:
            db.begin()
            cursor.execute(sql)
            db.commit()
        except Exception as e:
            db.rollback()
            print(e)

    # 查询数据库 inputline 
    select_sql = "SELECT * FROM words where word = '%s'" % inputLine
    cursor.execute(select_sql)
    info = cursor.fetchall()[0]
    print(info)
    

    db.close()
    cursor.close()
    pass


if __name__ == '__main__':
    run()