Question:
I want to connect MySQL RDS DB using python from raspberrypi.(i want to get seq from MySQL table ‘face’ using select query.)
and I have an error but i can not fix it.
This is rds mysql connection code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
import rds_config import pymysql rds_host = rds_config.rds_host name = rds_config.rds_user password = rds_config.rds_pwd db_name = rds_config.rds_db conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=10) with conn.cursor() as cur: cur.execute("select seq from face") conn.commit() |
rds_config:
|
1 2 3 4 5 6 |
rds_host='rds endpoint' rds_port=3306 rds_user='user' rds_pwd='password' rds_db='db name' |
and This is traceback:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Traceback (most recent call last): File "getRds.py", line 18, in conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=10) File "/usr/local/lib/python2.7/dist-packages/pymysql/__init__.py", line 94, in Connect return Connection(*args, **kwargs) File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 327, in __init__ self.connect() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 598, in connect self._request_authentication() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 862, in _request_authentication auth_packet = self._process_auth(plugin_name, auth_packet) File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 933, in _process_auth pkt = self._read_packet() File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 683, in _read_packet packet.check_error() File "/usr/local/lib/python2.7/dist-packages/pymysql/protocol.py", line 220, in check_error err.raise_mysql_exception(self._data) File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.InternalError: (1049, u"Unknown database 'bsb-rds'") |
i alread added ip address in vpc security group and public access is on.
it was possible to connect through mysql cli or workbench.
can anyone help me?
Answer:
tl;dr you need to create bsb-rd. Execute this command: create database bsb-rds either with cur.execute() in python or in your favorite sql cli.
If you get this error, good news! You can connect to your RDS instance. This means you have the security group set up right, the host url, username, password and port are all correct! What this error is telling you is that your database bsb-rds does not exist on your RDS instance. If you have just made the RDS instance it is probably because you have not created the database yet. So create it!
Here are two ways to do this
mysql cli
In your terminal run
mysql --host=the_host_address user=your_user_name --password=your_password
Then inside the mysql shell execute
create database bsb-rd;
Now try your code again!
Python with pymysql
|
1 2 3 4 5 6 |
import rds_config conn = pymysql.connect('hostname', user='username', passwd='password', connect_timeout=10) with conn.cursor() as cur: cur.execute('create database bsb-rd;') |
I came to this page looking for a solution and didn’t get it. I eventually found the answer and now share what helped me.