natta
Posts: 3
Joined: Wed May 01, 2019 1:52 pm

Need help with Python MySQL (pymysql)

Wed May 01, 2019 2:11 pm

I have already wrote python program for insert data to MySQL but it's not working.
Remark : This code is working when run in python for window 10 but not working in Raspberry Pi
Can anyone here help me? Thank you very much.

here is the code:

Code: Select all

import pymysql
import time

while True:
    db = pymysql.connect(host="169.254.94.100", user="root", passwd="password", port = 3306, database="pythondb")
    cursor = db.cursor()
    
    query = "INSERT INTO python_db(P, Q, S) VALUES(1,2,3)"

    print(query)
    cursor.execute(query)
    db.commit()
    print("INSERT COMPLETE")

    time.sleep(1)

and this is error

  • Traceback (most recent call last):
    File "/home/pi/.local/lib/python3.5/site-packages/pymysql/connections.py", line 583, in connect
    **kwargs)
    File "/usr/lib/python3.5/socket.py", line 712, in create_connection
    raise err
    File "/usr/lib/python3.5/socket.py", line 703, in create_connection
    sock.connect(sa)
    socket.timeout: timed out

    During handling of the above exception, another exception occurred:

    Traceback (most recent call last):
    File "/home/pi/Desktop/PyMySQL_Insert.py", line 9, in <module>
    db = pymysql.connect(host="169.254.94.100", user="root", passwd="password", port = 3306, database="pythondb")
    File "/home/pi/.local/lib/python3.5/site-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
    File "/home/pi/.local/lib/python3.5/site-packages/pymysql/connections.py", line 325, in __init__
    self.connect()
    File "/home/pi/.local/lib/python3.5/site-packages/pymysql/connections.py", line 630, in connect
    raise exc
    pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '169.254.94.100' (timed out)")

scotty101
Posts: 3562
Joined: Fri Jun 08, 2012 6:03 pm

Re: Need help with Python MySQL (pymysql)

Wed May 01, 2019 3:14 pm

I'm guessing that the MySQL server is on a separate computer and not run on the Raspberry Pi?

If so, has MySQL been configured to accept connections from other hosts? Root user access from remote hosts is disabled by default

https://stackoverflow.com/questions/147 ... n-to-mysql
Electronic and Computer Engineer
Pi Interests: Home Automation, IOT, Python and Tkinter

User avatar
DougieLawson
Posts: 35113
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Need help with Python MySQL (pymysql)

Wed May 01, 2019 8:59 pm

169.254.94.100 is a link local address (assigned by an interface when it can't reach the DHCP server on the network). That suggests your RPi is not properly connected through your router to the system with the MySQL server.

Run the following commands

ip addr
ip route
ping -c3 $(ip route | awk '/default/ {print $3}')
ping -c3 8.8.8.8
ping -c3 google.com
cat /etc/resolv.conf
for i in $(awk '/nameserver/ {print $2}' /etc/resolv.conf); do ping -c3 $i; done
[PRESS ENTER] # here if you cut'n'paste this stuff


To help us determine what's wrong with your networking.
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a hippy & doctor free zone.

natta
Posts: 3
Joined: Wed May 01, 2019 1:52 pm

Re: Need help with Python MySQL (pymysql)

Sat May 04, 2019 5:19 am

scotty101 wrote:
Wed May 01, 2019 3:14 pm
I'm guessing that the MySQL server is on a separate computer and not run on the Raspberry Pi?

If so, has MySQL been configured to accept connections from other hosts? Root user access from remote hosts is disabled by default

https://stackoverflow.com/questions/147 ... n-to-mysql

Thank you for your advice. I’m using MySQL server on seperate computer. After I try to set enable remote host on MySQL server it woking.

Thank you very much :D :D

natta
Posts: 3
Joined: Wed May 01, 2019 1:52 pm

Re: Need help with Python MySQL (pymysql)

Sat May 04, 2019 5:20 am

DougieLawson wrote:
Wed May 01, 2019 8:59 pm
169.254.94.100 is a link local address (assigned by an interface when it can't reach the DHCP server on the network). That suggests your RPi is not properly connected through your router to the system with the MySQL server.

Run the following commands

ip addr
ip route
ping -c3 $(ip route | awk '/default/ {print $3}')
ping -c3 8.8.8.8
ping -c3 google.com
cat /etc/resolv.conf
for i in $(awk '/nameserver/ {print $2}' /etc/resolv.conf); do ping -c3 $i; done
[PRESS ENTER] # here if you cut'n'paste this stuff


To help us determine what's wrong with your networking.

Thank you very much for your advice :D :D :D

Return to “Python”