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.
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.
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"
}`
- 'text' in the room:
send following data:
`{
"room":"test123",
"username":"smk",
"msg":"I am new "
}
`
- Now leave the room:
send following data:
`
{
"username":"smk",
"room":"test123"
}
`
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)
Top comments (3)
Hi, I wanted to let you know that all your queries made to the database (even if for theoretical demonstration) are vulnerable to SQL injections.
With that said, thanks for the tutorial!
Thanks for pointing out this, Just wrote queries only for tutorial purpose.
Hello, I am a student who wants to do the project in Korea. I'm leaving a comment because I have a question. There is a phenomenon where the letters are broken when typing Korean on the flutter, is there a way to fix it? I would really appreciate it if you could contact me at ysng9017@naver.com.