DEV Community 👩‍💻👨‍💻

Cover image for Flutter & Python Web-Socket Ft. Socket-IO (Part 2)
Md. Mobin
Md. Mobin

Posted on

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

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

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

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

Database

Lets create database:

  • Create new database.
create database database_name;
Enter fullscreen mode Exit fullscreen mode
  • Select created database.
use database_name;
Enter fullscreen mode Exit fullscreen mode
  • 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)
);

Enter fullscreen mode Exit fullscreen mode
  • 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
);
Enter fullscreen mode Exit fullscreen mode

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;")
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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)
Enter fullscreen mode Exit fullscreen mode

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

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)
Enter fullscreen mode Exit fullscreen mode
  • 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)
Enter fullscreen mode Exit fullscreen mode
  • 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)
Enter fullscreen mode Exit fullscreen mode

Done!!!!! Lets test it.

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

output1

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

output2

  • Now leave the room:

send following data:

{
"username":"smk",
"room":"test123"
}

output3

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

Stay Tuned....

Follow me:

Top comments (0)

🌚 Browsing with dark mode makes you a better developer by a factor of exactly 40.

It's a scientific fact.