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.
[1]:
import awswrangler as wr
import pandas as pd
df = pd.DataFrame({
"id": [1, 2],
"name": ["foo", "boo"]
})
Connect using the Glue Catalog Connections¶
[2]:
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)¶
[3]:
with con_redshift.cursor() as cursor:
for row in cursor.execute("SELECT 1"):
print(row)
[1]
Loading data to Database¶
[4]:
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¶
[5]:
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)
[5]:
id | name | |
---|---|---|
0 | 1 | foo |
1 | 2 | boo |
[6]:
con_redshift.close()
con_mysql.close()
con_postgresql.close()
con_sqlserver.close()