2

I have a problem with mySQL

I installed it on my raspberry pi - python 3 virtual environment with the following command:

sudo pip install mysql-connector

I have the following script:

import mysql.connector

mydb = mysql.connector.connect(
    host="127.0.0.1",
    port="11300",
    user="pi",
    passwd="1234"
 )

print(mydb)

But nothing happens, no error, no connection, ... I found the port number with the command 'netstat' under TCP connection

Werbrouck Bram
  • 21
  • 1
  • 4

1 Answers1

1

after execute you need to open cursor, to get result you can open cursors like:

mycursor.fetchall() returns iterable object.

for row in mycursor.fetchall():
    print(row)

mycursor.fetchone() fetches first row from result

print(mycursor.fetchone())

P.S. are you sure that you've installed that package properly?

e.g.: for ubuntu you can install this package for python3 by typing:

apt install python3-mysql.connector

Lasha Kitia
  • 141
  • 7
  • The installation was successful, When I type `mysql --help` in the prompt, and look at `port` I have a value `0` is this normal? and now with `netstat` I don't see the TCP/IP anymore ?? – Werbrouck Bram Aug 17 '18 at 13:48
  • port value `0` is normal. you've executed `mycursor.execute("SHOW DATABASE")` can you please try: `mycursor.execute("SHOW DATABASES")` and get rows by opening cursor – Lasha Kitia Aug 17 '18 at 14:04
  • this is what I have `import mysql.connector try: mydb = mysql.connector.connect(user='pi',password='1234',host='127.0.0.1',port='11300',database='test') mycursor = mydb.cursor() mycursor.execute("SHOW DATABASES") mycursor.fetchall() except: print("error")` But still I got nothing, It's like the prompt is waiting for something, but I can do anything.. – Werbrouck Bram Aug 17 '18 at 14:18
  • after executing try running this: `for row in mycursor.fetchall(): print(row)` – Lasha Kitia Aug 17 '18 at 14:20
  • @WerbrouckBram I've edited main answer, check examples there – Lasha Kitia Aug 17 '18 at 14:23
  • still the same problem. Is there a way to check the connections/settings/... ? – Werbrouck Bram Aug 17 '18 at 14:23
  • you can use `mydb.is_connected()` to get info about connected or not, You can disconnect `mydb.disconnect()` and connect: `mydb.connect()` – Lasha Kitia Aug 17 '18 at 14:27
  • btw can you connect directly to mysql console? `mysql -u pi -p -P 11300` enter and then type password – Lasha Kitia Aug 17 '18 at 14:29
  • I tried port 3306 again and now have the following error: `mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'pi'@'localhost'` – Werbrouck Bram Aug 17 '18 at 14:33
  • Seems like you have mysql on port `3306`. but strange, if you specify wrong port, `mysql.connector` should throw an exception: `mysql.connector.errors.InterfaceError` – Lasha Kitia Aug 17 '18 at 14:36
  • `sudo mysql -u pi -p` gives me an option to enter a password? I tried my normal login password from the raspberry, but when I hit enter, I got the message again`mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'pi'@'localhost'` – Werbrouck Bram Aug 17 '18 at 14:42
  • and what if you connect using port? `sudo mysql -u pi -p -P 11300` – Lasha Kitia Aug 17 '18 at 14:44
  • THen I have to enter a password (so I take the same pasword which I use to log in into the raspberry pi) but... access denied :( – Werbrouck Bram Aug 17 '18 at 14:52
  • I found something: I think but have no id what to do with it.. `MariaDB [mysql]> SELECT User, host, plugin FROM mysql.user; +------+-----------+-------------+ | User | host | plugin | +------+-----------+-------------+ | root | localhost | unix_socket | ` – Werbrouck Bram Aug 17 '18 at 14:53
  • hope [this](https://stackoverflow.com/questions/16287559/mysql-adding-user-for-remote-access) helps – Lasha Kitia Aug 17 '18 at 15:04