QQ chat robot developed by Python — assistant robot for college entrance examination volunteer filling

Approaching the college entrance examination season, a QQ group robot assisting volunteer filling is made here to record the specific implementation process.
The effects achieved are as follows:

This article is a series of articles. For a complete reproduction, please read it in the following order:

• Developing QQ chat robot with Python — deployment and adjustment of yes sauce
• Crawler practice 2 — Construction of professional score information database in Colleges and Universities
• QQ chat robot developed by Python — assistant robot for college entrance examination volunteer filling

environment

The environment used in this paper is as follows:

• Python 3.7
• Sublime Text 3
• go-cqhttp
• Yes sauce
• MYSQL
• Navicat for MYSQL

requirement analysis

The functions of the robot are as follows:

• The user enters the school name and returns the basic information of the school
• The user enters a score and returns a piece of information
• The user enters the score and returns the corresponding allelic score of previous years
• The user enters the school name and returns the school score line
• The user enters the school name to return to the school enrollment major
• The user enters the school name + major name to return the major score line of the school
• User input score recommendation institution (advanced function)
• User input score + professional recommendation institution (advanced function)

The data required for these functions are as follows:

• School basic information
• A piece of information
• School enrollment major
• School score line
• School professional score

This article only records the implementation of the first three functions. See the next article for the subsequent implementation

Data acquisition

Acquisition of basic information of colleges and Universities

See previous article:Crawler practice 2 — Construction of professional score information database in Colleges and Universities

Data acquisition by segment

Directly toThis websiteDownload the excel attachment from the. After downloading, open it manually and copy the data to a table

Program implementation

Implementation of QQ robot framework

Yes sauce, an open-source robot, is used here and is developed based on it. See the previous article for the specific implementation:Developing QQ chat robot with Python — deployment and adjustment of yes sauce

Database implementation

Here, Navicat is used to import the data obtained in the previous step into the database.

1. First, create a new database, and just choose the name:

   

   New database

2. Then insurfaceRight click there and selectimport wizard：

   

   import wizard

3. Select the corresponding file format:

   

   Select file format

4. Then select the imported data file and continue to default to the next step until the page. Modify the character length in the figure to meet the data conditions, and then click Next:

   

   Set data

5. After that, there is still no brain operation. Finally, click start. After importing, click close. Double click the table to see that it has been successfully imported:

   

   Successfully imported

The same is true for importing a piece of information. It should be noted that after importing the information, execute SQL statements to process the data:

UPDATE table_ Name set type = 'Liberal Arts' where type =' Liberal Arts'

Interface implementation

1. First, in the source code of yes saucesend_messageFiles in foldertalk_to_user.pyAdd interface to:

   

   Add entry

2. Then insend_messageFiles in folderword_detect.pyAdd the corresponding function in:

def college_data(msg):
  pass
def score2grade_data(msg):
  pass

3. Encapsulate the operation of the database to facilitate subsequent function calls:

def searchDatabase(sql):
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = 'password',
        charset = 'utf8',
        db = 'database_name'
    )
    cur = conn.cursor()
    cur.execute(sql)
    result = cur.fetchone()
    cur.close()
    conn.close()
    return result

Implementation of college information interface

Next, improve the functions of the new interface in the previous step:

For the data sent by the user, search the corresponding university information in the database:

sql = "SELECT * FROM table_name WHERE collegeName='{name}'".format(name=msg)
result = searchDatabase(sql)

If any information is returned, splice the information:

ReturnStr = "[{name}] \ n short name: {Jiancheng} \ n subordinate department: {Department} \ n college Code: {collegecode} \ n \ n \ u0001f4cd school address \ n {address} \ n \ n \ u0001f338 school features \ n {collegefeatures} \ n \ n \ u0001f335 school profile \ n {thesummary} \ n \ n for details, see: https://baike.baidu.com/item/ One of the city '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '''4] , urlname=quote(msg))

There is a small trick, that is, adding an Emoji expression to the returned data, which is referred to hereThis blog, the Emoji code used can be found inThis websiteFound on, of which+Number with corresponding number0Just make it up

Then return in the way of yes sauce:

return [True, returnStr]

One section by one + equipotential interface implementation

1. First, process the data sent by the user:

msgList = msg. Split ('branch ')
    if len(msgList)==2:
        try:
            Thetype = msglist [0] + 'family'
            theScore = int(msgList[1])

Then build an SQL statement to query the latest section by section information:

sql = "SELECT * FROM table_name WHERE score='{score}' AND year='{year}' AND type='{theType}'".format(score=theScore, year=currentYear, theType=theType)
result = searchDatabase(sql)

For the returned information, use its bit information for secondary query:

sql2 = "SELECT * FROM table_name WHERE type='{type}' AND year={currentYear} AND ABS(grade-{theGrade})=(SELECT MIN(ABS(grade-{theGrade})) FROM table_name WHERE type='{type}' AND year={currentYear})".format(type=theType, theGrade=theGrade, currentYear=(currentYear-1))
result2 = searchDatabase(sql2)

The SQL statement here is used to search the nearest position from this position in previous years and return all its information

Then, the query results of two SQL statements are spliced and returned:

ReturnStr = "the position corresponding to {thescore} score is: [{grade}] \ n [{counts}] people share your score \ n \ nthe position corresponds to the number of previous years: \ n number of year score positions \ n {dengweifen}" format(theScore=theType + str(theScore), grade=theGrade, counts=theCounts, dengweifen=dengweifenStr)
return [True, returnStr]

Considering that some data coverage is incomplete, such as the score is less than 100 and there is no corresponding bit information in the database, use SQL statement to search the bit of the highest score, lowest score and last place separately:

sql5 = "SELECT MAX(score), MIN(score), MAX(grade) FROM table_name WHERE year='{year}' AND type='{theType}'".format(year=currentYear, theType=theType)
result5 = searchDatabase(sql5)

If the value entered by the user is greater than the highest score, it is considered to be the first; On the contrary, if the value entered by the user is less than the lowest score, it is considered to be the last one:

                if theScore >= result5[0]:
                    theGrade = 1
                elif theScore <= result5[1]:
                    theGrade = result5[2]

Add this code to the previous code

Realization effect

So far, the first three basic functions of the robot have been fully realized, and the final effect is shown in the figure:

College information query

Allelic query