Trying to connect multiple computers to a MySQL server

Question

I'm not sure if this is python question, or a mysql question, but I want to be able to connect to a mysql server on one of my laptops from another laptop using python. I intend to allow the laptop connecting to the server to be able to change anything inside the database when it is connected and to be able to get the information from the database to show onto a GUI. I have already made the GUI on the laptop that is hosting the mysql server and it works exactly how I want it to, but now I want an external laptop to do the same without hosting the server itself, and I plan on having multiple computers to do the same. I want to know what would be the best approach to do this because I have looked online, and I found out I can do this by downloading the mysql workbench and then connecting to the server from the workbench, but I want to be able to do this though python only. Below is an example of the code I use to connect to the mysql server. I'm also using pycharm just incase that helps.

import mysql.connector  # make sure the python interpreter has 'mysql-connector-python' downloaded

# this sets up the connection to the mysql database where all the list information will be obtained from
mydb = mysql.connector.connect(host="xxx",
                               user="xxx",
                               passwd="xxx",
                               database="xxx"
                               )
mycursor = mydb.cursor()
mycursor.execute("select items from test where inuse = 0")
databaselistunsorted2 = [i[0] for i in # this makes the column turn its contents into a list for
                         mycursor.fetchall()]
# python
databaselist2 = databaselistunsorted2
print(databaselist2)

I have, but the server said that those computer did not have access. — neo_codex, Aug 25 '21 at 17:15
Are these computers connected to the same network ? and does the database have ip restriction ? — Snowfire, Aug 25 '21 at 17:18
In addition to @Snowfire's answer, sometimes firewall blocks incoming requests even if they originate from the same network. You might want to disable it or add an exception. — tugrul, Aug 25 '21 at 17:22
I'm not sure about the ip restriction, but yes, the computers are on the same network — neo_codex, Aug 25 '21 at 17:22
I just tried it, and the error it gave me said," No connection could be made because the target machine actively refused it" — neo_codex, Aug 25 '21 at 17:51

score 2 · Answer 1 · answered Aug 25 '21 at 17:42

It appears to be a MySql question.

You need to set MySql to allow remote connections.

To test it you can check if port 3306 is open on the computer with the MySql database, or whichever port you set it to if you changed from default.

Try running this from your computer that you want to use python from eventually, just as a test, and get it working first. Switching out the IP and user and such. There will need to be access allowed for you to connect remotely and this will help you verify that.

mysql -u user -h 192.168.1.28:3306 -p password

for more info see:

connecting to mysql server on another PC in LAN

If you get errors in the python once you can connect from the command line you can run this to see what the errors are:

import mysql.connector
from mysql.connector import errorcode

try:
  cnx = mysql.connector.connect(user='scott', password='password',
                                 host='127.0.0.1',
                                 database='employees')
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
    print('It Worked!')
    cnx.close()

https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

good luck!

Trying to connect multiple computers to a MySQL server

1 Answers1