{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "[![AWS SDK for pandas](_static/logo.png \"AWS SDK for pandas\")](https://github.com/aws/aws-sdk-pandas)\n", "\n", "# 7 - Redshift, MySQL, PostgreSQL, SQL Server and Oracle\n", "\n", "[awswrangler](https://github.com/aws/aws-sdk-pandas)'s Redshift, MySQL and PostgreSQL have two basic functions in common that try to follow Pandas conventions, but add more data type consistency.\n", "\n", "- [wr.redshift.to_sql()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.redshift.to_sql.html)\n", "- [wr.redshift.read_sql_query()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.redshift.read_sql_query.html)\n", "- [wr.mysql.to_sql()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.mysql.to_sql.html)\n", "- [wr.mysql.read_sql_query()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.mysql.read_sql_query.html)\n", "- [wr.postgresql.to_sql()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.postgresql.to_sql.html)\n", "- [wr.postgresql.read_sql_query()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.postgresql.read_sql_query.html)\n", "- [wr.sqlserver.to_sql()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.sqlserver.to_sql.html)\n", "- [wr.sqlserver.read_sql_query()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.sqlserver.read_sql_query.html)\n", "- [wr.oracle.to_sql()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.oracle.to_sql.html)\n", "- [wr.oracle.read_sql_query()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.oracle.read_sql_query.html)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Install the optional modules first\n", "!pip install 'awswrangler[redshift, postgres, mysql, sqlserver, oracle]'" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "\n", "import awswrangler as wr\n", "\n", "df = pd.DataFrame({\"id\": [1, 2], \"name\": [\"foo\", \"boo\"]})" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Connect using the Glue Catalog Connections\n", "\n", "- [wr.redshift.connect()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.redshift.connect.html)\n", "- [wr.mysql.connect()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.mysql.connect.html)\n", "- [wr.postgresql.connect()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.postgresql.connect.html)\n", "- [wr.sqlserver.connect()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.sqlserver.connect.html)\n", "- [wr.oracle.connect()](https://aws-sdk-pandas.readthedocs.io/en/3.9.2b1/stubs/awswrangler.oracle.connect.html)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "con_redshift = wr.redshift.connect(\"aws-sdk-pandas-redshift\")\n", "con_mysql = wr.mysql.connect(\"aws-sdk-pandas-mysql\")\n", "con_postgresql = wr.postgresql.connect(\"aws-sdk-pandas-postgresql\")\n", "con_sqlserver = wr.sqlserver.connect(\"aws-sdk-pandas-sqlserver\")\n", "con_oracle = wr.oracle.connect(\"aws-sdk-pandas-oracle\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Raw SQL queries (No Pandas)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[1]\n" ] } ], "source": [ "with con_redshift.cursor() as cursor:\n", " for row in cursor.execute(\"SELECT 1\"):\n", " print(row)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Loading data to Database" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "wr.redshift.to_sql(df, con_redshift, schema=\"public\", table=\"tutorial\", mode=\"overwrite\")\n", "wr.mysql.to_sql(df, con_mysql, schema=\"test\", table=\"tutorial\", mode=\"overwrite\")\n", "wr.postgresql.to_sql(df, con_postgresql, schema=\"public\", table=\"tutorial\", mode=\"overwrite\")\n", "wr.sqlserver.to_sql(df, con_sqlserver, schema=\"dbo\", table=\"tutorial\", mode=\"overwrite\")\n", "wr.oracle.to_sql(df, con_oracle, schema=\"test\", table=\"tutorial\", mode=\"overwrite\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Unloading data from Database" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " | id | \n", "name | \n", "
---|---|---|
0 | \n", "1 | \n", "foo | \n", "
1 | \n", "2 | \n", "boo | \n", "