WayScript allows many different data pipelines to work more efficiently and effectively. With one control point for all our data intersections, we can leverage the power of WayScript to build powerful tools using data from a variety of sources. In this example, we'll take a look at how we can use SQL database data and pass it to another source.
- First we'll need the dependencies to make this project work. Create a requirements.txt file for your project and include the following libraries:
flask pandas mysql-connector-python
- Install these libraries using your terminal or command prompt via pip:
pip install -r requirements.txt
- Next, the code we'll use for this project will take credentials of our database, pull records from it, and convert it to an html table we can pass to an endpoint. That code looks like this:
import os import mysql.connector import pandas as pd # The below pass and user are invalid, displayed for educational purposes only mydb = mysql.connector.connect( host='lcpbq9az4jklobvq.cbesntdyhwsb.us-east-1.rds.amazonaws.com', user='srwmp6imz5v549os', password='j7otwjk6utq3k3m9', port=3306, database='c9h0n4i2bk671ofb' ) # Get data sql = "SELECT * FROM earnings;" mycursor = mydb.cursor() mycursor.execute(sql) myresult = mycursor.fetchall() print(myresult) df = pd.DataFrame() for x in myresult: df2 = pd.DataFrame(list(x)).T df = pd.concat([df, df2]) df.to_html('templates/sql-data.html')
- If you do not wish to include raw usernames and passwords for your database table then there is a secrets manager within WayScript (or on your local machine as well.) You can set these secrets as environment or secret vars and call them using code that looks like:
password = os.environ.get('<VARIABLE_NAME>')
- This will create a html file inside your templates directory. ( If using wayscript, you will need to manually create this templates directory within the file manager before this works. )
- Then we can host our template using the wayscript deploy trigger and use either a node or flask server to serve the html template.
For help in serving html templates please view the WayScript docs on how to host servers.