Python Planetscale DB MySQL Connection

Question

I have been trying to make a Python program that connects to a Planetscale MySQL DB, I have used 2 libraries that are mysql-connector-python and mysqlclient.

I think I have entered the correct details every time with both but it hasn't worked.

I tried Planetscale's recommended way which is the following (it didn't work for me btw) :

import MySQLdb # pip install mysqlclient

# This is planetscale's copy/paste method to connect to the DB

conn = MySQLdb.connect(
  host=[HOST],
  user=[USER],
  passwd=[PASSWORD],
  db=[DATABASE],
  ssl_mode = "VERIFY_IDENTITY",
  ssl      = {
    "ca": "/etc/ssl/cert.pem"
  }
)

This is the current code I'm using

import mysql.connector # pip install mysql-connector-python

config = {
    "user": hidden for security purposes,
    "password": hidden for security purposes,
    "host": hidden for security purposes,
    "database": hidden for security purposes,
    "ssl_verify_identity": True,
    "ssl_ca": "/etc/ssl/cert.pem",
}
conn = mysql.connector.connect(**config)

cursor = conn.cursor()

cursor.execute("""CREATE TABLE IF NOT EXISTS qr_table (
    type VARCHAR(255),
    date VARCHAR(255),
    path VARCHAR(255),
    unique_id VARCHAR(255)
)""")

conn.commit()
cursor.close()
conn.close()

This is the error I'm getting

Traceback (most recent call last):
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\connection_cext.py", line 268, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: SSL connection error: SSL_CTX_set_default_verify_paths failed

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Axelr\PycharmProjects\PC01\main\Special Main\ARC QR Generator\SQL Script.py", line 26, in <module>
    conn = mysql.connector.connect(**config)
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\pooling.py", line 286, in connect
    return CMySQLConnection(*args, **kwargs)
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\connection_cext.py", line 101, in __init__
    self.connect(**kwargs)
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\abstracts.py", line 1108, in connect
    self._open_connection()
  File "C:\Users\Axelr\Python coding\lib\site-packages\mysql\connector\connection_cext.py", line 273, in _open_connection
    raise get_mysql_exception(
mysql.connector.errors.InterfaceError: 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed

Process finished with exit code 1

Can someone please help me make a connection that works?

Also, it wouldn't be a problem to use another library to connect.

[Python 3.10]

I added a first try of answer using the official the documentation to check the use of the connector which seems wrong. If you could provide more details about the error you are getting it would be helpful (traceback for instance) — AlexTorx
– AlexTorx, Commented Apr 15, 2023 at 16:52
I have now updated the question with much more information including code and errors. — axelrothing
– axelrothing, Commented Apr 15, 2023 at 17:49
Thanks for updated ! This looks like : stackoverflow.com/questions/54485148/… — AlexTorx
– AlexTorx, Commented Apr 16, 2023 at 7:56
Usually SSL issues are related to : 1/ not having an SSL client on the machine running the script 2/ Having misconfigured SSL on database side (wrong certificate for instance) — AlexTorx
– AlexTorx, Commented Apr 16, 2023 at 7:57
Plus you can have certificates at two levels : host certificate (for the URL of the server that hosts the database) and at the database level (that secures access to the data itself) — AlexTorx
– AlexTorx, Commented Apr 16, 2023 at 7:58

axelrothing · Accepted Answer · 2023-04-16 10:16:43Z

2

I have fixed the issue.

The problem was the SSL and that I used a dictionary to store the details, then used **config to use the dictionary in the connection.

Here is the correct and working code for me:

import mysql.connector as sql

conn = sql.connect(host="*Hidden For Security Purposes*",
                   database="*Hidden For Security Purposes*",
                   user="*Hidden For Security Purposes*",
                   password="*Hidden For Security Purposes*",
                    )

Thanks to everyone helping me with this problem and i hope this StackOverflow question will help many with the same problem.

answered Apr 16, 2023 at 10:16

axelrothing

1511 gold badge1 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

theprogrammer Over a year ago

getting "access denied for user my-pc-username".

axelrothing Over a year ago

Then it is probably the wrong details, if you find that the details are correct, try to update mysql-connector-python to the newest version.

AlexTorx · Accepted Answer · 2023-04-15 16:24:25Z

0

You should just not be using lists for input parameters I guess. Simply use strings for all parameters. Also, you should check documentation for correct parameters names (see https://mysqlclient.readthedocs.io/user_guide.html#mysqldb)

A working piece of code should be :

import MySQLdb

conn = MySQLdb.connect(
  host=HOST,
  user=USER,
  password=PASSWORD,
  database=DATABASE,
  ssl_mode = "VERIFY_IDENTITY",
  ssl      = {
    "ca": "/etc/ssl/cert.pem"
  }
)

answered Apr 15, 2023 at 16:24

AlexTorx

8056 silver badges10 bronze badges

1 Comment

axelrothing Over a year ago

I'm sorry for the confusion, the brackets around where just for people to see that its where i have placed the correct details, i already have seen the documentation and it hasn't worked for me. The reason i hide the details is for security purposes.

Collectives™ on Stack Overflow

Python Planetscale DB MySQL Connection

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related