Created
November 22, 2025 14:03
-
-
Save victorhcm/42f467d4c408fc8dd38510611939761b to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import pandas as pd | |
| @pd.api.extensions.register_dataframe_accessor("to_sql_statements") | |
| class SQLSelectAccessor: | |
| """ | |
| Defines a pandas DataFrame extension to export a df to a simple table using | |
| SQL statements for use in BigQuery. | |
| Example | |
| ------- | |
| >>> import pandas as pd | |
| >>> data = {'a': [1, 3, 4], 'b': ['abc', 'xyz', 'mno'], 'c': [10.5, 20.3, 30.1]} | |
| >>> df = pd.DataFrame(data) | |
| >>> sql_query = df.to_sql_statements() | |
| >>> print(sql_query) | |
| SELECT 1 AS a, 'abc' AS b, 10.5 AS c UNION ALL | |
| SELECT 3 AS a, 'xyz' AS b, 20.3 AS c UNION ALL | |
| SELECT 4 AS a, 'mno' AS b, 30.1 AS c | |
| """ | |
| def __init__(self, pandas_obj: pd.DataFrame): | |
| self._df = pandas_obj | |
| def __call__(self) -> str: | |
| def format_value(value): | |
| if isinstance(value, str): | |
| return f"'{value}'" | |
| return str(value) | |
| sql_statements = self._df.apply( | |
| lambda row: f"SELECT {', '.join([f'{format_value(row[col])} AS {col}' for col in self._df.columns])}", | |
| axis=1 | |
| ) | |
| return " UNION ALL\n".join(sql_statements) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment