Connect to MariaDB

[jgrincho]

@ccc Indeed the MariaDB module does not install but the mysql-connector does. I can get to the server but I start with endless problems to get anything out of it. And somewhere I read the mariadb module bypasses all these problems for python3. My question is if anyone managed to access mariaddb from pythonista and how.

[jgrincho]

@jgrincho let me be more precise: The mariadb module installs but does not work probably due to the C code. The mysql-connector-python installs and installs dependencies...

I found this article: (https://stackoverflow.com/questions/73244027/character-set-utf8-unsupported-in-python-mysql-connector)
which does not solve the problem but which seems to identify it.

If I code a wrong password, I get a message indicating so... with the correct password I get a message concerning the charset unsupported.

Any help would be most welcome... otherwise I will try to use a different DB on a server supported by pythonista. Any help for this option?

[JonB]

@jgrincho did you try using a password without special characters?

Also the last entry in that SO talks about specifying charset directly.

[JonB]

@JonB https://www.reddit.com/r/mariadb/comments/wa96kh/mysqlconnectorerrorsprogrammingerror_character/?utm_source=share&utm_medium=android_app&utm_name=androidcss&utm_term=1&utm_content=share_button

You might try installing MySQL connector 8.0.29. or, seems like this was maybe fixed in 8.0.31.

[DavinE]

@jgrincho,
Did you get it to work?
For me it runs without problems.

[jgrincho]

@DavinE Hi. I finally manage to get it working from my iPad to a Raspberry PI running MariaDB. I am now using PyMySQL as my module and it works fine... https://github.com/PyMySQL/PyMySQL

[jgrincho]

@JonB Tried no password, hacked the utf8 to be utf8mb4 but no success. Fortunately found (https://github.com/PyMySQL/PyMySQL) which is working fine...

[jgrincho]

@jgrincho https://pymysql.readthedocs.io/en/latest/user/examples.html Working code:

import pymysql.cursors

connection = pymysql.connect(host='192.168.1.80',
	user='user',
	password='123456',
	database='DataBase',
	charset='utf8mb4',
	cursorclass=pymysql.cursors.DictCursor)
														
with connection:
	with connection.cursor() as cursor:
		sql = "SELECT `shortname` FROM `users` WHERE `userid`=%s"
		cursor.execute(sql, ('1',))
		result = cursor.fetchone()
		print(result)

[jgrincho]

@DavinE Can you share what you have done?

[DavinE]

@jgrincho

This is my working code with MariaDB on a Synology

import mysql.connector

#FUNKTION Class main
class main():
    #FUNKTION __init__
    def __init__(self):
        self.MySQL_tests()

    #FUNKTION MariaDB
    def MariaDB(self):
        try:
            self.connection_SQL = mysql.connector.connect(
                user = 'username',
                password = 'Passwd in base64!',
                host = 'ip adresse',
                port = 3307,
                database = 'database',
                autocommit = True
            )
        except mysql.connector.Error as e:
            print(e)
        self.cursor_SQL = self.connection_SQL.cursor()

    #FUNKTION MySQL_tests
    def MySQL_tests(self):
        self.MariaDB()
        try:
            self.cursor_SQL.execute(
                """
                SELECT device_ID, role, mail
                FROM users
                WHERE Name = %s
                """,
                [
                    'Test'
                ],
            )
            print(self.cursor_SQL.fetchone())
        except mysql.connector.Error as e:
            print(e)
        finally:
            self.cursor_SQL.close()
            self.connection_SQL.close()
            
if __name__ == '__main__':
    main()

[dealycont]

I am now using PyMySQL as my module and it works fine...

[jgrincho]

@DavinE Many thanks!

[jgrincho]

@dealycont Indeed... that is also my way too go...