We'll show you how to get a Python application to write to and read from a MySQL database. This is very useful for storing user data, OAuth tokens, and much much more.
To run this example, you'll need a MySQL instance, and this example assumes that you're using an Amazon RDS instance in the "US East" region. For details on how to use a database hosted elsewhere, please see these instructions.
Note: these instructions apply to RDS instances created within the EC2-Classic environment only. This tutorial will be updated soon to include setup instructions for use with EC2-VPC, but in the meantime, you can check out Amazon's instructions on working with RDS in VPC.
1 Log in to Temboo. If you don't already have an account, you can register for free here.
2Make sure you've downloaded the Temboo Python SDK and that you've added it to your development environment as described in our getting started tutorial.
3If you don't have one, you'll also need an Amazon AWS account, which you can create here.
4Create a new MySQL RDS instance from the RDS Management Dashboard.
Getting ready to create an RDS MySQL instance by setting the region to US East
5Configure your RDS instance by adding a security rule to allow access by Temboo. If you're familiar with the RDS command line tools, you should follow these instructions. Otherwise, we'll guide you through the process using one of our Amazon Choreos.
DBSecurityGroupName value if you changed it from Amazon's default value.
The AuthorizeDBSecurityGroupIngress Choreo, ready to run.
6Now you're all set to get started with the Python part of this example. You'll need to overwrite the placeholders in code below with information about your database, available from Instances pane of the RDS dashboard, so let's gather that up now. You'll need:
The Instances pane of the RDS dashboard, which contains the details you need to put in your Python code.
7For this example, let's imagine that your application has some user data, and it needs to update or insert a user record based on whether or not that user is already in the database. We'll start by creating a a table using the MySQL > RunCommand Choreo as shown in the screenshot below.
Create a new table in your RDS MySQL instance
Here's the SQL statement you'll need to paste into the SQL input:
CREATE table users ( id INT NOT NULL, first_name VARCHAR(255), last_name VARCHAR(255), access_token VARCHAR(255), PRIMARY KEY (id) );
8 Now that you've created a table, you can try interacting with the database from your Python code. The code below uses the generated code for the MySQL > JSONToDB Choreo. It has been modified so that a JSON array containing user data is used as the BatchFile input in order to perform an UPSERT on the database table. We've also defined constants for sample user data and database credentials.
9Copy the code below into your Python editor, substitute in your AWS account details, and try it out.
from temboo.core.session import TembooSession
from temboo.Library.MySQL import JSONToDB
import json
"""
UPDATE THE VALUES OF THESE CONSTANTS WITH YOUR OWN CREDENTIALS
"""
# Use these constants to define the set of credentials that will be used
# to connect with Temboo.
TEMBOO_ACCOUNT_NAME = "ACCOUNT_NAME"
TEMBOO_APP_NAME = "APP_NAME"
TEMBOO_APP_KEY = "APP_KEY"
# These constants define the database credentials for your MySQL instance.
DATABASE_SERVER = "YOUR_DB_SERVER"
DATABASE_NAME = "YOUR_DB_NAME"
DATABASE_USERNAME = "YOUR_DB_USERNAME"
DATABASE_PASSWORD = "YOUR_DB_PASSWORD"
# Sets the table name created in Step 7 of this tutorial.
DATABASE_TABLE_NAME = "users"
# For this example, let's imagine your application has parsed the following user data,
# and needs to perform UPDATES or INSERTS depending on whether or not the record exists
# in the DB already.
USER_ID = "1001"
FIRST_NAME = "John"
LAST_NAME = "Smith"
ACCESS_TOKEN = "CAAIfEoEqOAEBAFqusQeUEKpWeG13sG85A7Mbyq"
"""
END CONSTANTS; NOTHING BELOW THIS POINT SHOULD NEED TO BE CHANGED
"""
class DatabaseHelper(object):
def __init__(self):
"""
Set up Temboo session and create a user object.
"""
self.session = TembooSession(TEMBOO_ACCOUNT_NAME, TEMBOO_APP_NAME,
TEMBOO_APP_KEY)
# Create a user object used to perform the DB UPSERT
userObject = {"id": USER_ID}
userObject['first_name'] = FIRST_NAME
userObject['last_name'] = LAST_NAME
userObject['access_token'] = ACCESS_TOKEN
userList = []
userList.append(userObject)
self.userInfo = json.dumps(userList)
print(self.userInfo)
def upsert(self):
"""
Execute UPSERT
"""
# Instantiate the Choreo using a session object
jSONToDBChoreo = JSONToDB(self.session)
# Get an InputSet object for the Choreo
jSONToDBInputs = jSONToDBChoreo.new_input_set()
# Set the Choreo inputs
jSONToDBInputs.set_DatabaseName(DATABASE_NAME)
jSONToDBInputs.set_BatchFile(self.userInfo)
jSONToDBInputs.set_Password(DATABASE_PASSWORD)
jSONToDBInputs.set_Username(DATABASE_USERNAME)
jSONToDBInputs.set_TableName(DATABASE_TABLE_NAME)
jSONToDBInputs.set_Server(DATABASE_SERVER)
jSONToDBInputs.set_BatchMode("upsert")
# Execute the Choreo
jSONToDBInputsResults = jSONToDBChoreo.execute_with_results(jSONToDBInputs)
# Print the Choreo outputs
print ("The result of the upsert operation was: " + jSONToDBInputsResults.get_Success())
def main(self):
"""
Wraps things all up. Run upsert query.
"""
self.upsert()
if __name__ == "__main__":
instance = DatabaseHelper()
instance.main()
We're all finished! This Python application can create or update a user record in a MySQL database based on whether or not the user exists in the table already. Now you're ready to tackle all sorts of database operations from your Python code. You can find lots more valuable data to store by checking out the 2000+ Choreos in our Library.
We're always happy to help. Just email us at [email protected], and we'll answer your questions.