If you are connecting to the AWS AMI MapD instance, use the public IP address as the host and the instance ID as the password. Here is the connection pattern:

from pymapd import connect
connection = connect(user="mapd", password= "{password}", 
     host="{my.host.com}", dbname="mapd")
cursor = connection.cursor()

Here is an example of what a filled out connect instantiation could look like:

connection = connect(user="mapd", password= "i-0ed5ey62se2w8eed3", 
  host="ec2-54-212-133-87.us-west-2.compute.amazonaws.com", dbname="mapd")