Bot for VK in Python with MySQL in an hour

Foreword

I apologize for not writing about working with the database in the first article, but this part is aimed at working with MySQL.

Part 3 will be a kind of addition, where we will consider working with the keyboard for the bot and this will end the cycle of articles.

Training

After the last article on hand, we should have the following code:
')

import vk_api from vk_api.utils import get_random_id from vk_api.bot_longpoll import VkBotLongPoll, VkBotEventType import pymysql.cursors import requests def getConnection(): connection = pymysql.connect(host='you_host', user='you_user', password='you_password', db='you_db' charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) return connection <anchor>habracut</anchor> vk_session = vk_api.VkApi(token="a6f87v8c9a9sa87a7af9a0f9f9v8a6s6c5b5m6n8bds09asc8d7b87d87bd87n") vk = vk_session.get_api() longpoll = VkBotLongPoll(vk_session, "637182735") #  for event in longpoll.listen(): if event.type == VkBotEventType.MESSAGE_NEW: #       if event.obj.text != '': #       if event.from_user: #  vk.messages.send( user_id=event.obj.from_id, random_id=get_random_id(), message=event.obj.text) 

Let's create a bot that will return us a result positive or negative. For example, "Life" and "Dead". Then put it in the database and send this result to us in the future.

To begin, we must have our own database with a table already prepared.

If someone does not know how to create tables, then here is the code that needs to be inserted while being in the mysql console:

 CREATE DATABASE `firstbot`; CREATE TABLE `mode`(Id_User INT(11), Mode VARCHAR(6) DEFAULT("NOT"), PRIMARY KEY(Id_User)); 

At this frame of our table will be ready. More information about creating and changing tables can be found on off. site (link will be below).

Create a function that will randomly return Dead or Live:

 #   random import random def randomMode(): #      1  200 number = random.randint(1, 200) if number % 2 == 0: return 'Live' else: return 'Dead' 

Now we need to make a function that will send our values ​​to the database.

 def addToDatabase(functionMode, x): #   connection = getConnection() try: #     cursor = connection.cursor() #  sql = "INSERT INTO mode (Id_User, Mode) VALUES (%s, %s)" #       cursor.execute(sql, (x, functionMode)) connection.commit() #     ,     except: sql = "UPDATE mode SET Mode WHERE Id_User = %s" cursor.execute(sql, (functionMode, x)) connection.commit() finally: connection.close() return functionMode 

We have created a function that will add a person to the database or end his work if he is already there.

But besides the fact that we enter values ​​into the database, we need to get them. To do this, create such a function.

 def selectFromDatabase(idUser): connection = getConnection() try: cursor = connection.cursor() sql = "SELECT Mode FROM mode WHERE Id_User = %s" cursor.execute(sql, (idUser)) #        for i in cursor: modeSend = i['Mode'] connection.commit() except: print("") finally: connection.close() return modeSend 

All that is left for us is to assemble the code completely and add a couple of lines so that everything would work as we want.

 if event.obj.text == '': if event.from_user: idUser = event.obj.from_id vk.messages.send( user_id=event.obj.from_id, random_id=get_random_id(), message=" : " + addToDatabase(randomMode(), idUser)) if event.obj.text == ' ': if event.from_user: idUser = event.obj.from_id vk.messages.send( user_id=event.obj.from_id, random_id=get_random_id(), message=" : " + selectFromDatabase(idUser)) 

And so we will get a response from our bot.

Total

With the help of the work done, we created a bot who knows how to work with MySQL. At this its functionality does not end there. It is much wider and more, and limited only by your imagination.
The code is not ideal and I only wrote it to show how MySQL works. If you know ways better, feel free to apply them. I will also accept any help on improving the current code.

useful links

Official PyMySQL Page
MySQL official page
PyMySQL Documentation in Russian
Documentation vk_api
Documenting API Vkontakte