Installation of Mysql connector on Pi

[vajra11]

I want to send data from raspberry pi to MySQL database server running on windows. I am trying to connect to it from a python module.

I need to install packages on raspberry. I spent an hour to find correct packages to connect Mysql database

This is packages I think needs to be installed on raspberry Pi

Code: Select all

sudo apt-get update
sudo apt-get upgrade

sudo apt-get install mysqldb

sudo apt-get -y install python-mysql.connector

pip install mysql-python

Am I missing any packages to connect Mysql Database

[DougieLawson]

That apt install and your pip install are both installing the same piece. Choose one or the other.

You also need to ensure your MySQL/MariaDB server is binding to the 0.0.0.0 address to allow remote connections.

[vajra11]

That apt install and your pip install are both installing the same piece. Choose one or the other.

Is it what you are saying

Code: Select all

sudo apt-get install mysql-python

sudo apt-get  install python-mysql.connector

suso apt-get install pymysql

sudo apt-get install mysqldb

[DougieLawson]

Yes.

[vajra11]

Yes
You also need to ensure your MySQL/MariaDB server is binding to the 0.0.0.0 address to allow remote connections
.

Thanks @DougieLawson I am following this blog https://howtoraspberrypi.com/enable-mys ... pberry-pi/ to enable remote connection to MySQL server on windows laptop.

I followed up to flush privileges; step. I am struggling after that, I think I have to configure MySQL database installed on window to allow remote connection. In blog there is sudo command and but sudo command doesn't work on windows.

welcome for your any advice

[DougieLawson]

Sorry I don't know how MySQL works (or fails) on Windows, I've never dreamt of running it on anything other than Linux.

[vajra11]

Sorry I don't know how MySQL works (or fails) on Windows, I've never dreamt of running it on anything other than Linux.

I followed the instruction for binding address.

then I check remote connection with below program

Do you see anything wrong in program

Code: Select all

import MySQLdb
 
db = MySQLdb.connect(host="192.168.0.105",  # your host 
                     user="vajra",       # username
                     passwd="vajra11",     # password
                     db="temp1")   # name of the database
 
# Create a Cursor object to execute queries.
cur = db.cursor()
 
# Select data from table using SQL query.
cur.execute("SELECT * FROM examples")
 
# print the first and second columns      
for row in cur.fetchall() :
    print row[0], " ", row[1]

What is this error

mysql.png (50.63 KiB) Viewed 32861 times

[DougieLawson]

Stop using MySQLdb switch to mysql.connector.

sudo apt install python{,3}-mysql.connector
https://dev.mysql.com/doc/connector-pyt ... mples.html

It works much the same as MySQLdb.

Code: Select all

import mysql.connector

db = mysql.connector.connect(user='vajra', password='vajra11',
                              host='192.168.0.105',
                              database='temp1')
                              
# ... rest of stuff as before goes here ...

[vajra11]

Stop using MySQLdb switch to mysql.connector.

sudo apt install python{,3}-mysql.connector
https://dev.mysql.com/doc/connector-pyt ... mples.html

That's not supporting

Code: Select all

Traceback (most recent call last):
  File "dbtest1.py", line 5, in <module>
    database='temp1') 
  File "/usr/lib/python3/dist-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 94, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/abstracts.py", line 722, in connect
    self._open_connection()
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 211, in _open_connection
    self._ssl)
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 141, in _do_auth
    auth_plugin=self._auth_plugin)
  File "/usr/lib/python3/dist-packages/mysql/connector/protocol.py", line 102, in make_auth
    auth_data, ssl_enabled)
  File "/usr/lib/python3/dist-packages/mysql/connector/protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
  File "/usr/lib/python3/dist-packages/mysql/connector/authentication.py", line 191, in get_auth_plugin
    "Authentication plugin '{0}' is not supported".format(plugin_name))
mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported
pi@raspberrypi:~/temp1 $ sudo python3 dbtest1.py
Traceback (most recent call last):
  File "dbtest1.py", line 5, in <module>
    database='temp1') 
  File "/usr/lib/python3/dist-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 94, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/abstracts.py", line 722, in connect
    self._open_connection()
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 211, in _open_connection
    self._ssl)
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 141, in _do_auth
    auth_plugin=self._auth_plugin)
  File "/usr/lib/python3/dist-packages/mysql/connector/protocol.py", line 102, in make_auth
    auth_data, ssl_enabled)
  File "/usr/lib/python3/dist-packages/mysql/connector/protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
  File "/usr/lib/python3/dist-packages/mysql/connector/authentication.py", line 191, in get_auth_plugin
    "Authentication plugin '{0}' is not supported".format(plugin_name))
mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

[DougieLawson]

Try
sudo apt purge python{,3}-mysql*
sudo pip install mysql-connector-python
sudo pip3 install mysql-connector-python # if using python3.

The old junk in DebIan Stretch doesn't support the new authentication scheme.

[vajra11]

Try
sudo apt purge python{,3}-mysql*
sudo pip install mysql-connector-python
sudo pip3 install mysql-connector-python # if using python3.

The old junk in DebIan Stretch doesn't support the new authentication scheme.

Tried

Code: Select all

pi@raspberrypi:~/temp1 $ python dbtest1.py
Traceback (most recent call last):
  File "dbtest1.py", line 5, in <module>
    database='temp1') 
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/__init__.py", line 183, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 100, in __init__
    self.connect(**kwargs)
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/abstracts.py", line 736, in connect
    self._open_connection()
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 246, in _open_connection
    self._ssl)
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 166, in _do_auth
    self._auth_switch_request(username, password)
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 210, in _auth_switch_request
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1049 (42000): Unknown database 'temp1'

[DougieLawson]

That's an improvement, you're past the authentication error. You now need to ensure that your userid is allowed to make remote connections and can access your "temp1" database.

https://stackoverflow.com/questions/162 ... ote-access

[vajra11]

That's an improvement, you're past the authentication error. You now need to ensure that your userid is allowed to make remote connections and can access your "temp1" database.

https://stackoverflow.com/questions/162 ... ote-access

following are the steps I have done for remote connection

Code: Select all

Enter password: ******
CREATE USER 'database1'@'localhost' IDENTIFIED BY 'db123';
CREATE USER 'database1'@'%' IDENTIFIED BY 'db123';
GRANT ALL ON *.* TO 'database1'@'localhost';
GRANT ALL ON *.* TO 'database1'@'%';

Code: Select all

create database test3;
use test3;
CREATE TABLE tablename ( id int unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO tablename ( id, name ) VALUES ( null, 'Sample data' );
describe tablename;

program

Code: Select all

import mysql.connector

db = mysql.connector.connect(user='database1', password='db123',
                              host='192.168.0.104',
                              database='temp3') 
# Create a Cursor object to execute queries.
cur = db.cursor()
 
# Select data from table using SQL query.
cur.execute("SELECT * FROM tablename")
 
# print the first and second columns    
    
for row in cur.fetchall() :
    print row[0], " ", row[1]

output error

Code: Select all

pi@raspberrypi:~/temp1 $ python dbtest1.py
Traceback (most recent call last):
  File "dbtest1.py", line 10, in <module>
    cur.execute("SELECT * FROM tablename")
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 566, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 537, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 436, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'temp3.tablename' doesn't exist

[DirkS]

Having a quick look at the code / messages...
I see both temp3 and test3 as the database name...
You create test3 and try to connect to temp3

[vajra11]

Having a quick look at the code / messages...
I see both temp3 and test3 as the database name...
You create test3 and try to connect to temp3

Thanks for catching error. Thanks DougieLawson I appreciate your effort and help

I don't have much knowledge on Mysql programming. My first priority was setting remote connection. I just checking remote connection with sample program. I would learn by writing simple program

I am not sure but I think remote connection has been successfully.

Code: Select all

pi@raspberrypi:~/temp1 $ python dbtest1.py
1 Sample data

[ivaring]

Hi there,

I'm trying of getting connected Arduino and Raspberry devices.
The idea is having some kind of access control from Arduino then sending data to a mysql server installed on Raspberry (already working).
The question is how to install new libraries on Raspbian Arduino IDE in order to get this working, then creating necessary databases and sending data as needed.

Thanks.