0

I am new to all this stuff

I am trying to access my website database (phpmyadmin) from python. All the tutorials available on internet are using xampp localhost which is perfectly working but When I replace localhost with my website address or host ip at it gives error ([Errno 11001] getaddrinfo failed)

import pymysql

db = pymysql.connect(host='localhost', user='root',passwd='')
cursor = db.cursor()
query = ("SHOW DATABASES")
cursor.execute(query)
for r in cursor:
    print(r)

OUTPUT

('indus',)
('information_schema',)
('mysql',)
('performance_schema',)
('phpmyadmin',)
('test',)
('wordpress',)

replacing localhost with website address or domain ip gives output

Traceback (most recent call last):
  File "C:\Users\HYSTOU\PycharmProjects\HelloWorld\venv\lib\site-packages\pymysql\connections.py", line 613, in connect
    sock = socket.create_connection(
  File "C:\Users\HYSTOU\AppData\Local\Programs\Python\Python39\lib\socket.py", line 823, in create_connection
    for res in getaddrinfo(host, port, 0, SOCK_STREAM):
  File "C:\Users\HYSTOU\AppData\Local\Programs\Python\Python39\lib\socket.py", line 954, in getaddrinfo
    for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
socket.gaierror: [Errno 11001] getaddrinfo failed

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\HYSTOU\PycharmProjects\HelloWorld\indusExtractingFloat.py", line 26, in <module>
    db = pymysql.connect(host='https://www.rubick.org/', user='root',passwd='')
  File "C:\Users\HYSTOU\PycharmProjects\HelloWorld\venv\lib\site-packages\pymysql\connections.py", line 353, in __init__
    self.connect()
  File "C:\Users\HYSTOU\PycharmProjects\HelloWorld\venv\lib\site-packages\pymysql\connections.py", line 664, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'https://www.rubick.org/' ([Errno 11001] getaddrinfo failed)")

Using other methods gives (Times out)

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
Usman Khawar
  • 30
  • 6
  • `getaddrinfo failed` That sounds like a DNS issue, can you ping the website from the terminal ? ( https://stackoverflow.com/a/7335972/17965313 ) – vinalti Feb 10 '22 at 10:22
  • 1
    which host You set when trying to connect to database on server? In stacktrace I see this value ```https://www.rubick.org/```. Try set host parameter like rubick.org – Олег Кулаков Feb 10 '22 at 10:26
  • I ping the website give: Packets: Sent = 4, Received = 3, Lost = 1 (25% loss), Tried rubick.org/www.rubick.org gives "2003, "Can't connect to MySQL server on 'rubick.org' (timed out)"" – Usman Khawar Feb 10 '22 at 11:23

1 Answers1

0

When you define the host connection, basically you say that you want to connect to a given node on the web. If the value of host is localhost, that means that your application will connect to the database of the same computer.

Yes, you can connect to a remote host as well, but it's not necessarily as easy as you would like. A problem that you definitely have is that you set the host value to https://www.rubick.org/, which is incorrect, because HTTPS is a protocol that you may use to reach a resource from your browser, but it's not part of the host's name. Try www.rubick.org instead.

Assuming that the host name is correct, you may also need to perform some further steps in order to ensure that this connection is successful, such as connecting to a VPN or any other steps necessary that your sysadmin may specify that you need to do.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • I tried www.rubick.org and it gives error "Can't connect to MySQL server on 'www.rubick.org' (timed out)". I have a strong feeling that we might need to enable it on cpannel Hosting somewhere. It seems that online hosting is not accepting any request to phpymadmin apart from cpannel – Usman Khawar Feb 10 '22 at 11:18
  • @UsmanKhawar CPanel runs on your server as well as your database. CPanel connects to it via localhost. Remote accessing a connection is unsafe, so it is normal that this is switched off for the time being. But, before we explore your options, can you tell me why you want to connect to your database from your local machine? Because this very much seems to be a misuse of your connection, even if you are successful in it. I would strongly recommend that you create a backup of your database, download it, install MySQL on your dev environment and connect to your local copy, so experimental – Lajos Arpad Feb 10 '22 at 12:03
  • @UsmanKhawar code will not do data damage on your prod environment's database. – Lajos Arpad Feb 10 '22 at 12:04
  • The reason I want remote access because the data is updated on the SQL database on hour basis. And I already create so many reports to upload on my Company's ERP then I had to create a separate report in ODS format for website. So I was hopping that already with a single code I upload data on my ERP and create reports for my further analysis same code ends with updating the same file on my website as well. Instead of creating a new ods file and loging into CPANNEL then phpmyadmin databases upload. – Usman Khawar Feb 12 '22 at 06:36
  • I had a backup of my database. I called the hosting service provider and he said that you have a shared hosting so yes its locked by default you need to go through further steps to unlock it and access your database remotely – Usman Khawar Feb 12 '22 at 06:37
  • @UsmanKhawar if you have ssh access to the server, then you can connect to the database via ssh tunnelling (see https://stackoverflow.com/questions/21903411/enable-python-to-connect-to-mysql-via-ssh-tunnelling). But I advise you to avoid doing so and, instead, synchronize your local database with the remote one when needed. Connecting your dev env with prod database is unsafe. – Lajos Arpad Feb 12 '22 at 12:16