CODEWITHSUNDEEP
amazon-web-servicesamazon-rds
Jin Hong
Jin Hong

Reputation: 192

How to insert data to AWS RDS directly?

I have created the Mysql instance in AWS RDS.

I want to create a database and insert some data into it by hand directly.

Before I could insert data by Phpmyadmin.

But I lost my way in AWS

I have downloaded the mysql workbench for this. But it fails to connect to the instance with error message ( can not connect to the instance (60) )

I think it happens due to a firewall in AWS

I need the way or app to insert data by hand like phpmyadmin.

Upvotes: 1

Views: 8627

Answers (2)

razimbres
razimbres

Reputation: 5015

You can use Python's pyodbc library:

import pyodbc

server = 'tcp:instance.abcd12345.rds.amazonaws.com'
database = 'database4'
username = 'your_username'
password = 'your_Password'

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = cnxn.cursor()

cursor.execute('''

                INSERT INTO Table3(ID,LastName,FirstName,Address,City)
VALUES ('444', 'Schwartz', 'Paul','Wilshire Blvd, 2333', 'Los Angeles');

                ''')

cnxn.commit()

cursor.execute("SELECT * FROM Database4.dbo.Table3;")

row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()

Upvotes: 0

filipebarretto
filipebarretto

Reputation: 1952

The firewall you are referring to is the Security Group. Can you select your RDS instance, click on show details and make sure the security group attached to it has the inbound rule on port 3306 for source 0.0.0.0/0 (the best practice would be to only allow access from the ip range you will actually access it from)?

If that is ok, click on the VPC that was created for your RDS instance (still on the RDS details page), and on the VPC's page, on the left menu there is a security group option. There should be a "Created from the RDS Management Console" rule. Check if that is also configured to allow access to port 3306.

With those being allowed, the connection using mySQL Workbench should work. If not, might be something with the credentials in the connection.

Upvotes: 1

Related Questions