I have this conection to MySql in python with JayDeBeApi, using JDBC:
def data_JDBC(db_name, table, db, user, pwd):
db = db.lower()
db_name = db_name.lower()
print(db)
if db == 'postgresql':
sql_str = f"select * from {db}.{table}"
host='ec2-18-191-149-107.us-east-2.compute.amazonaws.com'
port='5432'
user=user
pwd = pwd
driver_name = 'org.postgresql.Driver'
driver_path = 'path/to/postgresql-42.2.12.jar'
elif db == 'mysql':
sql_str = f"select * from {table}"
host='localhost'
port='3306'
user=user
pwd = ""
driver_name = 'com.mysql.cj.jdbc.Driver'
driver_path = 'path/to/mysql-connector-java-8.0.20.jar'
print(driver_path)
connection_string=f'jdbc:{db}://'+ host+':'+ port +'/'+ db_name+'?useSSL=false&&serverTimezone=UTC&useLegacyDatetimeCode=false'
if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
jpype.attachThreadToJVM()
jpype.java.lang.Thread.currentThread().setContextClassLoader(jpype.java.lang.ClassLoader.getSystemClassLoader())
conn = jaydebeapi.connect(driver_name, connection_string,[user, pwd], jars=driver_path)
frame = pd.read_sql(sql_str, conn);
pd.set_option('display.expand_frame_repr', False)
conn.close()
return frame
This function returns a Pandas DataFrame, i have the jar files in the same directory. PostgreSQL works fine, but when i select for MySQL i get this error:
java.lang.RuntimeExceptionPyRaisable Traceback (most recent call last)
<ipython-input-426-311d4ecabc53> in <module>
----> 1 dfMysql=data_JDBC(db_name='fuentedatos', table='tests', db='MySql', user='root', pwd='')
2 dfMysql
...
java.lang.RuntimeExceptionPyRaisable: java.lang.RuntimeException: Class com.mysql.cj.jdbc.Driver not found
This is how i execute the function:
data_JDBC(db_name='fuentedatos', table='tests', db='MySql', user='root', pwd='')
For that particular error case there are two potential error causes.
1- Usually in the Java world the
java.lang.RuntimeException: Class driverClassName not found
exception is raised when we hadn't add the jar file's path to the CLASSPATH environment variable and that's what was exactly attempted to be explained here2- (the most likely one) It seems like Jaydebeapi doesn't support the establishment of multiple connections for different data sources at the same time (github issue).