In the last tutorial we build socket-IO server with some socket.on() events.

Part 1

Now we are going to connect MySQL Server with flask so that we can persist message send by an user in the specified room.

Install MySQL in your local machine.

We are going to follow the following structure for the database.

Lets create database:

Create new database.



create database database_name;

Select created database. ```

use database_name;


- Create user table.

create table user
(
userid int auto_increment primary key,
username varchar(50) not null,
roomId varchar(50) null,
constraint username unique (username)
);


- create table for message i.e. "chats" table.

create table chats
(
msg varchar(200) not null,
username varchar(200) not null,
room varchar(200) not null,
ts datetime not null
);


Now our database has been setup and lets connect with python script.



## **Integration MySQL Database in Flask APP:**


- Make connection with database.

Add followings lines below the line `socketio = SocketIO(app, cors_allowed_origins='*')` :

MYSQL Config

mysql = MySQL()

name of the database user

app.config['MYSQL_DATABASE_USER'] = 'username'

password of the database user

app.config['MYSQL_DATABASE_PASSWORD'] = 'yourpassword'

database name

app.config['MYSQL_DATABASE_DB'] = 'database_name'

Domain or Host,Keep it localhost if you are testing on localhost

app.config['MYSQL_DATABASE_HOST'] = 'localhost'

mysql.init_app(app)

connection

conn = mysql.connect()

Cursor for MySQL

cursor = conn.cursor()

create a new db if you have not created yet or remove comment from next line

cursor.execute("create database newdb;")

cursor.execute("use newdb;")


Now our connection has been made with database.

## Create functions for getting chats and adding new messages into database:

- lets create a function for getting chats for that we required roomId.
We will fetch only message & timestamp attributes from **chats** tables where roomId will be same as given by user.

def getChats(room):
query = "select msg,ts from chats where room='%s' order by ts ; " % room
cursor.execute(query)
msgLst = cursor.fetchall()
lst = []
for msg in msgLst:
lst.append({'msg': msg[0], 'ts': str(msg[1])})
return lst


- lets create another function for adding new messages into **chats** table.

For this we will required message text, roomId,username and  we will use System Date Time function for Time Stamp.

def addNewMsg(msg, room, username):
x = datetime.datetime.now()
try:
query = "insert into chats(msg, room, username,ts) values('%s','%s','%s','%s');" % (msg, room, username, x)
cursor.execute(query)
# committing the changes in database.
conn.commit()
except Exception as e:
print(e)


Lets move to next part where we have to edit Socket.on() Events.

## **Required Changes in Socket.on() Events:**

- Lets talk about 'join' socket.on() Event.

We have three different conditions.

1. User is joining first time that's mean we do not have data in user table. For this we need to write new user data into user table and inform the concerned room by sending  following message **"user_name has entered the room."**.

2. User already exist and but joined different room. We need to update his roomId before joining the room.

3. User Re-Entered same room without leaving it. No action Required.



![JOIN EVENT](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/urghl8sysytwqdtq4lr9.jpg)

replace **def join(message)** function with will following codes:

def join(message):
room = message['roomId']
username = message['username']
join_room(room)
query = "Select username,roomId from user where username='%s'" % username + ' ; '

cursor.execute(query)
user = cursor.fetchall()

# if user not exist then create new user
if len(user) == 0:
    try:
        query = "Insert into user values (0,'%s','%s')" % (username, room)
        cursor.execute(query)
        conn.commit()
        addNewMsg(msg=username + ' has entered the room.', username=username, room=room)

    except Exception as e:
        print(e)
else:
    if user[0][1] != room:
        query = "UPDATE user SET roomId = '%s' WHERE username = '%s';" % (room, username)
        cursor.execute(query)
        conn.commit()
        addNewMsg(msg=username + ' has entered the room.', username=username, room=room)
# getting all the messages
emit('message', {'msg': getChats(room)}, room=room)


- Now lets update socket.on('text') events with following lines of codes:

def text(message):
room = message['room']
username = message['username']
addNewMsg(msg=username + " : " + message['msg'], username=username, room=room)
emit('message', {'msg': getChats(room)}, room=room)


- Last and the Least update socket.on('left) events with following lines of codes:

def left(message):
room = message['room']
username = message['username']
addNewMsg(msg=username + ' has left the room.', username=username, room=room)
leave_room(room)

lst = getChats(room)
if len(lst) == 0:
    emit('message', {'msg': [{'msg': "No messages has been sent"}]})
else:
    emit('message', {'msg': lst}, room=room)




> **Done!!!!! Lets test it.**

- 'join' room :
send following data in join event:
`{
"roomId":"test123",
"username":"smk"
}`

![output1](https://imgur.com/6R6RHiS.png)

- 'text' in the room:
send following data:
`{
"room":"test123",
"username":"smk",
"msg":"I am new "
}
`

![output2](https://imgur.com/yk5JzH1.png)

- Now leave the room:

send following data:
`
{
"username":"smk",
"room":"test123"
}
`

![output3](https://imgur.com/NFAIIgu.png)

Hurray its done.


In Next Part: we will be creating a flutter web application for real time chat using emit and on events using flutter socket-IO client package.

Part 1 in case you missed: [Read here](https://dev.to/djsmk123/flutter-python-web-socket-ft-socket-io-part-1-3icf)

Stay Tuned....

- [**Source Code**](https://github.com/Djsmk123/web_socket_example_backend)

> Follow me:

- [GitHub](https://github.com//djsmk123)

- [LinkedIn](https://www.linkedin.com/in/md-mobin-bb928820b)

- [Twitter](https://twitter.com/smk_winner)

Flutter & Python Web-Socket Ft. Socket-IO (Part 2)