How to get list of all databases and run query on it in python

Question

I want to print all databases of 1 RDS and run a select query on it. Here, I am able to print all databases but want to run SELECT query on each database. Please suggest solution for it?

import boto.rds
import sys
import os
import MySQLdb

conn = boto.rds.connect_to_region("ap-southeast-1", aws_access_key_id='xxxx',aws_secret_access_key='xxx')
instances = conn.get_all_dbinstances()
db = instances[0] 
print "%s %s" % (instances, db.endpoint)

I used this as a code, but its not working

host_name=db.endpoint
print host_name 

db = MySQLdb.connect(host=host_name,user="dxx",passwd="xxx",db="xxx")
cursor = db.cursor()
cursor.execute("SHOW DATABASES")
db.commit()
numrows = int(cursor.rowcount)
for x in range(0,numrows):
    row = cursor.fetchone()
     if row[0].find('em') != -1:
        print row[0]
        sql="select * from row[0].T_USER where LoginId='%s'" % 'hello'
        cursor.execute(sql) 
        results = cursor.fetchone()
        print results[0]

Yes, I want to pass dbendpoint as a host, but it showed me TypeError: connect() argument 1 must be string, not tuple — qwerty
– qwerty, Commented Mar 16, 2015 at 4:26
Just print the db.endpoint, its returning you tuple, get hostname form that tuple. — NPatel
– NPatel, Commented Mar 16, 2015 at 4:32
I am not getting it, do I need to modify it using regular expression or something else? — qwerty
– qwerty, Commented Mar 16, 2015 at 4:36

huhushow · Accepted Answer · 2015-03-16 04:56:32Z

2

http://boto.readthedocs.org/en/latest/rds_tut.html

see this page. it useful.

db.endpoint look like this

(u'db-master-1.aaaaaaaaaa.us-west-2.rds.amazonaws.com', 3306)

it is python tuple and use like

host_name=db.endpoint[0]

and print all databases use simple query like

show databases;

edited Mar 16, 2015 at 4:56

answered Mar 16, 2015 at 4:46

huhushow

5474 silver badges13 bronze badges

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

1 Comment

qwerty Over a year ago

Could you please tell me how can I show all databases on 1 rds, because here connect needs db_name but I want to extract this list first and then execute select statement on all

Collectives™ on Stack Overflow

How to get list of all databases and run query on it in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related