You are not logged in.

#1 2023-01-18 16:26:30

RounakDutta
Member
From: West Bengal, India
Registered: 2022-02-11
Posts: 173
Website

[SOLVED] Mysql python connector not working.

I am using MariaDb which is a fork of mysql. I want python mysql connectivity so that I can get dbms resultset inside python. I also installed the python mysql connector package. Then I ran the following commands -

[rounak@archissexy 21:45 ~]$ sudo mysql
[sudo] password for rounak:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.9.4-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databasess;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'databasess' at line 1
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| Hotel              |
| information_schema |
| lol                |
| mysql              |
| performance_schema |
| project            |
| school             |
| sys                |
+--------------------+
8 rows in set (0.008 sec)

MariaDB [(none)]> quit
Bye
[rounak@archissexy 21:45 ~]$ python
Python 3.10.9 (main, Dec 19 2022, 17:35:49) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import mysql.connector as pol
>>> my = pol.connect(host="localhost",user="root",passwd="110820051234" database="lol")
  File "<stdin>", line 1
    my = pol.connect(host="localhost",user="root",passwd="110820051234" database="lol")
                                                         ^^^^^^^^^^^^^^^^^^^^^^^
SyntaxError: invalid syntax. Perhaps you forgot a comma?
>>> my = pol.connect(host="localhost",user="root",passwd="110820051234",database="lol")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.10/site-packages/mysql/connector/pooling.py", line 287, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/lib/python3.10/site-packages/mysql/connector/connection.py", line 137, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python3.10/site-packages/mysql/connector/abstracts.py", line 1108, in connect
    self._open_connection()
  File "/usr/lib/python3.10/site-packages/mysql/connector/connection.py", line 535, in _open_connection
    self._do_auth(
  File "/usr/lib/python3.10/site-packages/mysql/connector/connection.py", line 279, in _do_auth
    self._auth_switch_request(username, password)
  File "/usr/lib/python3.10/site-packages/mysql/connector/connection.py", line 332, in _auth_switch_request
    raise get_exception(packet)
mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'root'@'localhost'
>>> my = pol.connect(host="localhost",user="rounak",passwd="110820051234",database="lol")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.10/site-packages/mysql/connector/pooling.py", line 287, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/lib/python3.10/site-packages/mysql/connector/connection.py", line 137, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python3.10/site-packages/mysql/connector/abstracts.py", line 1108, in connect
    self._open_connection()
  File "/usr/lib/python3.10/site-packages/mysql/connector/connection.py", line 535, in _open_connection
    self._do_auth(
  File "/usr/lib/python3.10/site-packages/mysql/connector/connection.py", line 279, in _do_auth
    self._auth_switch_request(username, password)
  File "/usr/lib/python3.10/site-packages/mysql/connector/connection.py", line 332, in _auth_switch_request
    raise get_exception(packet)
mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'rounak'@'localhost'
>>>

I got errors idk why :'(

It says "Access denied for user 'rounak'@'localhost'" for some reason.

Last edited by RounakDutta (2023-02-06 17:13:20)

Offline

#2 2023-01-18 18:04:57

schard
Member
From: Hannover
Registered: 2016-05-06
Posts: 1,932
Website

Re: [SOLVED] Mysql python connector not working.

You forgot a comma to separate the arguments.  Also, why do you run the MySQL client as root?

I just realized that the syntax error is unrelated to your issue.
Is the user in question allowed to access the database lol? What does a use/select query using the MySQL client yield?

Last edited by schard (2023-01-18 18:09:49)

Offline

#3 2023-01-18 19:37:27

Trilby
Inspector Parrot
Registered: 2011-11-29
Posts: 29,444
Website

Re: [SOLVED] Mysql python connector not working.

Have you addeded a database user named 'rounak'?  Or 'root' for that matter, and / or is the password you are passing for root the password for the database root user, not the system root user? Note that neither of those user names refer to the linux system users; database users are completely different.

What are the results of the sql query `SELECT User, Host FROM mysql.user`?

Last edited by Trilby (2023-01-18 19:39:09)


"UNIX is simple and coherent..." - Dennis Ritchie, "GNU's Not UNIX" -  Richard Stallman

Offline

#4 2023-01-19 07:25:14

RounakDutta
Member
From: West Bengal, India
Registered: 2022-02-11
Posts: 173
Website

Re: [SOLVED] Mysql python connector not working.

Trilby wrote:

Have you addeded a database user named 'rounak'?  Or 'root' for that matter, and / or is the password you are passing for root the password for the database root user, not the system root user? Note that neither of those user names refer to the linux system users; database users are completely different.

What are the results of the sql query `SELECT User, Host FROM mysql.user`?

[rounak@archissexy 12:51 ~]$ sudo mysql
[sudo] password for rounak:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.9.4-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> Ctrl-C -- exit!
Aborted
[rounak@archissexy 12:51 ~]$ mairadb
bash: mairadb: command not found
[rounak@archissexy 12:53 ~]$ f
mariadb [enter/↑/↓/ctrl+c]
ERROR 1698 (28000): Access denied for user 'rounak'@'localhost'
[rounak@archissexy 12:53 ~]$ sudo mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.9.4-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT User, Host FROM mysql.user
    -> ;
+-------------+------------+
| User        | Host       |
+-------------+------------+
| rounak      | archissexy |
| mariadb.sys | localhost  |
| mysql       | localhost  |
| root        | localhost  |
+-------------+------------+
4 rows in set (0.011 sec)

MariaDB [(none)]>

Umm so I think I have a root user and rounak user.

Offline

#5 2023-01-19 07:33:30

Slithery
Administrator
From: Norfolk, UK
Registered: 2013-12-01
Posts: 5,776

Re: [SOLVED] Mysql python connector not working.

Don't run mariadb using sudo.


No, it didn't "fix" anything. It just shifted the brokeness one space to the right. - jasonwryan
Closing -- for deletion; Banning -- for muppetry. - jasonwryan

aur - dotfiles

Offline

#6 2023-01-19 08:00:45

RounakDutta
Member
From: West Bengal, India
Registered: 2022-02-11
Posts: 173
Website

Re: [SOLVED] Mysql python connector not working.

Slithery wrote:

Don't run mariadb using sudo.

I understand but when I try to run it without sudo I get this error  -

[rounak@archissexy 13:30 ~]$ mariadb
ERROR 1698 (28000): Access denied for user 'rounak'@'localhost'
[rounak@archissexy 13:30 ~]$
[rounak@archissexy 13:41 ~]$ mysql
ERROR 1698 (28000): Access denied for user 'rounak'@'localhost'
[rounak@archissexy 13:41 ~]$

Last edited by RounakDutta (2023-01-19 08:12:08)

Offline

#7 2023-01-19 13:55:11

Trilby
Inspector Parrot
Registered: 2011-11-29
Posts: 29,444
Website

Re: [SOLVED] Mysql python connector not working.

RounakDutta wrote:

I think I have a root user and rounak user.

root yes, rounak, kind of.  I'd assume that "archissexy" is your hostname, but I'm not sure if mariadb allows the actual hostname and localhost to be used interchangeably.  You do not have a rounak@localhost database user.  As for the passwords, you are using the database passwords for these users (from python) not the system passwords, right?

Have you also rechecked those passwords?  It seems every code sample you post is littered with failed attempts due to typos or improper commands.  So checking the passwords in your python code seems relevant.

As for it not running as a regular user that's because you are not following the documentation on how to start it.  Which instructions did you follow to install / configure mariadb?  You should use `mariadb -u username -p` (for localhost users).

Last edited by Trilby (2023-01-19 13:58:03)


"UNIX is simple and coherent..." - Dennis Ritchie, "GNU's Not UNIX" -  Richard Stallman

Offline

#8 2023-01-20 15:40:58

twelveeighty
Member
From: Alberta, Canada
Registered: 2011-09-04
Posts: 1,096

Re: [SOLVED] Mysql python connector not working.

RounakDutta wrote:
MariaDB [(none)]> SELECT User, Host FROM mysql.user
    -> ;
+-------------+------------+
| User        | Host       |
+-------------+------------+
| rounak      | archissexy |
| mariadb.sys | localhost  |
| mysql       | localhost  |
| root        | localhost  |
+-------------+------------+
4 rows in set (0.011 sec)

MariaDB [(none)]>

Umm so I think I have a root user and rounak user.

No, with MySQL/MariaDB, it's the #1 thing new users get confused over: you have a rounak@archissexy user. There is no such thing as a 'rounak' user (i.e. without the @ context). And the DB takes the @ part literally: 'localhost' != 'archissexy'. You have to explicitly grant rounak@localhost access to log in (step 1) and also grant that user access to the database you wish to use (step 2).

Offline

#9 2023-02-06 08:16:04

RounakDutta
Member
From: West Bengal, India
Registered: 2022-02-11
Posts: 173
Website

Re: [SOLVED] Mysql python connector not working.

Trilby wrote:
RounakDutta wrote:

I think I have a root user and rounak user.

root yes, rounak, kind of.  I'd assume that "archissexy" is your hostname, but I'm not sure if mariadb allows the actual hostname and localhost to be used interchangeably.  You do not have a rounak@localhost database user.  As for the passwords, you are using the database passwords for these users (from python) not the system passwords, right?

Have you also rechecked those passwords?  It seems every code sample you post is littered with failed attempts due to typos or improper commands.  So checking the passwords in your python code seems relevant.

As for it not running as a regular user that's because you are not following the documentation on how to start it.  Which instructions did you follow to install / configure mariadb?  You should use `mariadb -u username -p` (for localhost users).

Sorry for the late reply. I did this and now my mysql is not opening -

[rounak@archissexy 13:37 ~]$ sudo mysql
[sudo] password for rounak:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.9.4-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+-------------+------------+
| User        | Host       |
+-------------+------------+
| rounak      | archissexy |
| mariadb.sys | localhost  |
| mysql       | localhost  |
| root        | localhost  |
+-------------+------------+
4 rows in set (0.010 sec)

MariaDB [(none)]> CREATE USER 'rounak'@'localhost' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON mydb.* TO 'rounak'@'localhost';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+-------------+------------+
| User        | Host       |
+-------------+------------+
| rounak      | archissexy |
| mariadb.sys | localhost  |
| mysql       | localhost  |
| root        | localhost  |
| rounak      | localhost  |
+-------------+------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> DROP USER 'rounak'@'archissexy';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
| rounak      | localhost |
+-------------+-----------+
4 rows in set (0.002 sec)

MariaDB [(none)]> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| rounak      | localhost |
+-------------+-----------+
3 rows in set (0.001 sec)

MariaDB [(none)]> quit
Bye
[rounak@archissexy 13:44 ~]$ mysql
ERROR 1045 (28000): Access denied for user 'rounak'@'localhost' (using password: NO)
[rounak@archissexy 13:44 ~]$ sudo mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[rounak@archissexy 13:44 ~]$

Offline

#10 2023-02-06 14:21:25

Trilby
Inspector Parrot
Registered: 2011-11-29
Posts: 29,444
Website

Re: [SOLVED] Mysql python connector not working.

Slithery wrote:

Don't run mariadb using sudo.

Trilby wrote:

Which instructions did you follow to install / configure mariadb?  You should use `mariadb -u username -p` (for localhost users).

All that your last post reveals is that you had never set a password for the root database user and / or configured that account to allow logging in without a password.  This is odd, and not particularly wise, but not at all relevant to your current issues.  Though it may be a parallel result of the same root cause that you are following some totally broken third-party tutorial.

Last edited by Trilby (2023-02-06 14:31:32)


"UNIX is simple and coherent..." - Dennis Ritchie, "GNU's Not UNIX" -  Richard Stallman

Offline

#11 2023-02-06 17:13:09

RounakDutta
Member
From: West Bengal, India
Registered: 2022-02-11
Posts: 173
Website

Re: [SOLVED] Mysql python connector not working.

Thanks a lot Trilby, Slithery and all of you for helping  . I finally fixed my problem !!

[rounak@archissexy 22:32 ~]$ sudo mariadb -u root -p
[sudo] password for rounak:
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.9.4-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER 'rounak'@'localhost' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON mydb.* TO 'rounak'@'localhost';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> DROP USER 'rounak'@'archissexy';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
| rounak      | localhost |
+-------------+-----------+
4 rows in set (0.002 sec)

MariaDB [(none)]> quit
Bye
[rounak@archissexy 22:34 ~]$ mariadb -u rounak -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.9.4-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> Ctrl-C -- exit!
Aborted

Marking the post as [SOLVED].

Offline

#12 2023-02-06 17:15:06

Trilby
Inspector Parrot
Registered: 2011-11-29
Posts: 29,444
Website

Re: [SOLVED] Mysql python connector not working.

Yet you still ran mariadb with sudo.  Break that habit.


"UNIX is simple and coherent..." - Dennis Ritchie, "GNU's Not UNIX" -  Richard Stallman

Offline

Board footer

Powered by FluxBB