7 - Redshift, MySQL, PostgreSQL and SQL Server

Wrangler’s Redshift, MySQL and PostgreSQL have two basic function in common that tries to follow the Pandas conventions, but add more data type consistency.

• wr.redshift.to_sql()

• wr.redshift.read_sql_query()

• wr.mysql.to_sql()

• wr.mysql.read_sql_query()

• wr.postgresql.to_sql()

• wr.postgresql.read_sql_query()

• wr.sqlserver.to_sql()

• wr.sqlserver.read_sql_query()

import awswrangler as wr
import pandas as pd

df = pd.DataFrame({
    "id": [1, 2],
    "name": ["foo", "boo"]
})

Connect using the Glue Catalog Connections

• wr.redshift.connect()

• wr.mysql.connect()

• wr.postgresql.connect()

• wr.sqlserver.connect()

con_redshift = wr.redshift.connect("aws-data-wrangler-redshift")
con_mysql = wr.mysql.connect("aws-data-wrangler-mysql")
con_postgresql = wr.postgresql.connect("aws-data-wrangler-postgresql")
con_sqlserver = wr.sqlserver.connect("aws-data-wrangler-sqlserver")

Raw SQL queries (No Pandas)

with con_redshift.cursor() as cursor:
    for row in cursor.execute("SELECT 1"):
        print(row)

[1]

Loading data to Database

wr.redshift.to_sql(df, con_redshift, schema="public", table="tutorial", mode="overwrite")
wr.mysql.to_sql(df, con_mysql, schema="test", table="tutorial", mode="overwrite")
wr.postgresql.to_sql(df, con_postgresql, schema="public", table="tutorial", mode="overwrite")
wr.sqlserver.to_sql(df, con_sqlserver, schema="dbo", table="tutorial", mode="overwrite")

Unloading data from Database

wr.redshift.read_sql_query("SELECT * FROM public.tutorial", con=con_redshift)
wr.mysql.read_sql_query("SELECT * FROM test.tutorial", con=con_mysql)
wr.postgresql.read_sql_query("SELECT * FROM public.tutorial", con=con_postgresql)
wr.sqlserver.read_sql_query("SELECT * FROM dbo.tutorial", con=con_sqlserver)

	id	name
0	1	foo
1	2	boo

con_redshift.close()
con_mysql.close()
con_postgresql.close()
con_sqlserver.close()