You are only one humorous sentence away from your girlfriend. python teaches you how to quickly put interesting sentences into the database

Keywords: Python Database

Have you ever seen such a sentence:

"The people I love also love me. It's a miracle for me."

Or:

"Life without friends is like life without sunshine."

Or this:

"I will hold you, pull you and save you. I won't let go, but you want me to hold you, you know?"

Are some very interesting sentences, how to get these sentences and use them for yourself!

First, analysis field

First, we need to analyze the websites we need to obtain, and then find the fields we need. Here, we provide two websites for learning

https://api.fghrsh.net/hitokoto/rand/?encode=jsc&uid=3335

https://v1.hitokoto.cn/

Then let's analyze the fields we need.

Looking at the json above, the fields we need to obtain are hitokoto, source, author, and id

Similarly, when we analyze the second website, it is the same operation.

Second, establish database tables

After we can get the field data, we need to analyze how our table is displayed and stored.

So let's create the database first. To prevent facial expressions, we use utf8mb4

'''Create database'''
CREATE DATABASE `csdn_article` CHARACTER SET 'utf8mb4';

Next, let's create a data table:

'''Create data table'''
CREATE TABLE `linking_blink`  (
  `id` int unsigned NOT NULL,
  `linking_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'id',
  `text` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'content',
  `auther` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'author',
  `source` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'source',
  `from` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'channel',
  `passed` int(0) NULL DEFAULT NULL COMMENT '0,No, 1, yes, send',
  `insert_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

Third, insert database} method

We have established the table. Next, we need to create a scheme for inserting data, which is convenient for us to call when inserting and querying data.

#Establish a link pool to prepare for the future. The database needs to be changed to its own database.
POOL = PooledDB(
    creator=pymysql,  maxconnections=0,  mincached=3, maxcached=0,maxshared=3,
    blocking=True,  maxusage=None, setsession=[], ping=0,
    host='127.0.0.1',port=3306,user='root',password='root',database='csdn_article',charset='utf8mb4')

#insert data
def insert_text(linking_id,text,auther,source,from_w):
    db = POOL.connection()
    conn = db.cursor()
    conn.execute("INSERT INTO `linking_blink`(`linking_id`, `text`, `auther`,`source`, `from`, `passed`) VALUES ('%s', '%s', '%s', '%s','%s', 0);"%(linking_id,text,auther,source,from1))# Use the execute method to execute SQL statements
    data=db.commit()
    db.close()
    return data
#Query data by content
def select_text(text,from_w):
    db = POOL.connection()
    conn = db.cursor()
    conn.execute("SELECT * FROM `linking_blink` WHERE `text` = '%s' AND `from` = '%s' LIMIT 0, 1;"%(text,from_w))# Use the execute method to execute SQL statements
    data = conn.fetchall()
    db.close()
    return data
#Query data by id
def select_linking_id(linking_id,from_w):
    db = POOL.connection()
    conn = db.cursor()
    conn.execute("SELECT * FROM `linking_blink` WHERE `linking_id` = '%s' AND `from` = '%s' LIMIT 0, 1;"%(linking_id,from_w))# Use the execute method to execute SQL statements
    data = conn.fetchall()
    db.close()
    return data

Fourth, obtain website data

We have prepared the database, so let's get the website data and insert it into the database.

headers={'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36'}
def fghrsh(from_w="fghrsh"):
    response=requests.get(url="https://api.fghrsh.net/hitokoto/rand/?encode=jsc&uid=3335",headers=headers)
    if select_linking_id(response.json()["id"],from_w)==():
        if response.json()["source"] == "":
            insert_text(response.json()["id"],response.json()["hitokoto"],response.json()["author"],response.json()["author"],from_w)
        else:
            insert_text(response.json()["id"],response.json()["hitokoto"],response.json()["author"],response.json()["source"],from_w)
def hitokoto(from_w="hitokoto"):
    response=requests.get(url="https://v1.hitokoto.cn/",headers=headers)
    if select_linking_id(response.json()["id"],from_w)==():
        insert_text(response.json()["id"],response.json()["hitokoto"],response.json()["from_who"],response.json()["from"],from_w)

It's done successfully. Let's see the results! Successfully inserted into database

Fifth, complete code

import os,random
import json
import requests
import pymysql
from dbutils.pooled_db import PooledDB

#Link pool
POOL = PooledDB(
    creator=pymysql,  maxconnections=0,  mincached=3, maxcached=0,maxshared=3,
    blocking=True,  maxusage=None, setsession=[], ping=0,
    host='127.0.0.1',port=3306,user='root',password='root',database='csdn_article',charset='utf8mb4')

def insert_text(linking_id,text,auther,source,from_w):
    db = POOL.connection()
    conn = db.cursor()# Use the cursor() method to get the operation cursor
    conn.execute("INSERT INTO `linking_blink`(`linking_id`, `text`, `auther`,`source`, `from`, `passed`) VALUES ('%s', '%s', '%s', '%s','%s', 0);"%(linking_id,text,auther,source,from1))# Use the execute method to execute SQL statements
    data=db.commit()# Use the fetchone() method to get a piece of data
    db.close()
    return data

def select_text(text,from_w):
    db = POOL.connection()
    conn = db.cursor()# Use the cursor() method to get the operation cursor
    conn.execute("SELECT * FROM `linking_blink` WHERE `text` = '%s' AND `from` = '%s' LIMIT 0, 1;"%(text,from_w))# Use the execute method to execute SQL statements
    data = conn.fetchall()# Use the fetchone() method to get a piece of data
    db.close()
    return data

def select_linking_id(linking_id,from_w):
    db = POOL.connection()
    conn = db.cursor()# Use the cursor() method to get the operation cursor
    conn.execute("SELECT * FROM `linking_blink` WHERE `linking_id` = '%s' AND `from` = '%s' LIMIT 0, 1;"%(linking_id,from_w))# Use the execute method to execute SQL statements
    data = conn.fetchall()# Use the fetchone() method to get a piece of data
    db.close()
    return data
headers={'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36'}
def fghrsh(from_w="fghrsh"):
    response=requests.get(url="https://api.fghrsh.net/hitokoto/rand/?encode=jsc&uid=3335",headers=headers)
    if select_linking_id(response.json()["id"],from_w)==():
        if response.json()["source"] == "":
            insert_text(response.json()["id"],response.json()["hitokoto"],response.json()["author"],response.json()["author"],from_w)
        else:
            insert_text(response.json()["id"],response.json()["hitokoto"],response.json()["author"],response.json()["source"],from_w)
def hitokoto(from_w="hitokoto"):
    response=requests.get(url="https://v1.hitokoto.cn/",headers=headers)
    if select_linking_id(response.json()["id"],from_w)==():
        insert_text(response.json()["id"],response.json()["hitokoto"],response.json()["from_who"],response.json()["from"],from_w)
if __name__ == '__main__':
    for i in range(20):
        fghrsh()
        hitokoto()

Relevant recommendations:

Browser plug-in, easy - quick access to website source code

Python Tkinter implements various kinds of mouse tailing

Too much. Others use it for desktop application development, but this guy uses it to tease his sister (6) - invisible pop-up window

Posted by treppers on Thu, 16 Sep 2021 11:58:08 -0700

Programmer Group