I am trying to connect to a remote api, grab some json data, parse it, and the save it to a database running on a remote machine, the api calls and data parsing I have, but trying to setup my python script to connect to the database I get the following error:
mysql.connector.errors.ProgrammingError: Character set 'utf8' unsupported
I have tried changing my connection string to what seems to be the character set that my table is using, utf8mb4_generic_ci, but I still get the error, just with that rather than the utf8.
mysql+mysqlconnector://root:<password>@<ip>:3306/<Database>?charset=utf8mb4_generic_ci
Picture showing example table, with charset
I am using MariaDB 10.9.2 for my database, and phpMyAdmin to manage it. My code looks something like:
# Import libraries
from sqlalchemy import create_engine
# Setup sql connection
engine = create_engine('mysql+mysqlconnector://root:<Password>@<IP>:3306/<Database>')
connection = engine.connect()
# Run a sample query
result = connection.execute("SELECT * FROM users")
# Fetch the results
for row in result:
print(row)
# Close the connection
connection.close()
