version: 0.1.1
default:
MYSQL_DATABASE: ...
MYSQL_HOST: ...
MYSQL_PASSWORD: ...
MYSQL_PORT: 3306
MYSQL_USER: root
import mysql.connector
conn = mysql.connector.connect(
host='...',
password='...',
port=3306,
user='root',
database='...',
)
sql = """
SELECT
id
, email
FROM auth_user
"""
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
import pandas as pd
df = pd.DataFrame(rows, columns=['id', 'email'])
df.to_csv('default_repo/users.csv', index=False)
from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.mysql import MySQL
from os import path
@data_loader
def load_data_from_mysql(*args, **kwargs):
"""
Template for loading data from a MySQL database.
Specify your configuration settings in 'io_config.yaml'.
Docs: https://docs.mage.ai/design/data-loading
"""
query = 'Your MySQL query'
config_path = path.join(get_repo_path(), 'io_config.yaml')
config_profile = 'default'
with MySQL.with_config(ConfigFileLoader(config_path, config_profile)) as loader:
return loader.load(query)
from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.mysql import MySQL
from pandas import DataFrame
from os import path
if 'data_exporter' not in globals():
from mage_ai.data_preparation.decorators import data_exporter
@data_exporter
def export_data_to_mysql(df: DataFrame, **kwargs) -> None:
"""
Template for exporting data to a MySQL database.
Specify your configuration settings in 'io_config.yaml'.
Docs: https://docs.mage.ai/design/data-loading
"""
table_name = 'your_table_name'
config_path = path.join(get_repo_path(), 'io_config.yaml')
config_profile = 'default'
with MySQL.with_config(ConfigFileLoader(config_path, config_profile)) as loader:
loader.export(
df,
None,
table_name,
index=False,
if_exists='replace',
)
To overwrite a column type when running a python export block, simply specify the column name and type in the overwrite_types
dict in data exporter config
from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.mysql import MySQL
from pandas import DataFrame
from os import path
if 'data_exporter' not in globals():
from mage_ai.data_preparation.decorators import data_exporter
@data_exporter
def export_data_to_mysql(df: DataFrame, **kwargs) -> None:
"""
Template for exporting data to a MySQL database.
Specify your configuration settings in 'io_config.yaml'.
Docs: https://docs.mage.ai/design/data-loading
"""
table_name = 'your_table_name'
config_path = path.join(get_repo_path(), 'io_config.yaml')
config_profile = 'default'
overwrite_types = {'column_name': 'VARCHAR(255)'}
with MySQL.with_config(ConfigFileLoader(config_path, config_profile)) as loader:
loader.export(
df,
None,
table_name,
index=False,
if_exists='replace',
overwrite_types=overwrite_types,
)