def make_sql_table(table,
table_name,
dialect=None,
db_schema=None,
constraints=True,
connection=None):
"""
Generates a SQL alchemy table from an agate table.
"""
metadata = MetaData(connection)
sql_table = Table(table_name, metadata, schema=db_schema)
if dialect in INTERVAL_MAP.keys():
SQL_TYPE_MAP[agate.TimeDelta] = INTERVAL_MAP[dialect]
else:
SQL_TYPE_MAP[agate.TimeDelta] = Interval
for column_name, column in table.columns.items():
sql_type_kwargs = {}
sql_column_kwargs = {}
if constraints:
if isinstance(column.data_type, agate.Text):
# If length is zero, SQLAlchemy may raise "VARCHAR requires a length on dialect mysql".
sql_type_kwargs['length'] = table.aggregate(
agate.MaxLength(column_name)) or 1
# PostgreSQL and SQLite don't have scale default 0.
# @see https://www.postgresql.org/docs/9.2/static/datatype-numeric.html
# @see https://www.sqlite.org/datatype3.html
if isinstance(
column.data_type,
agate.Number) and dialect in ('mssql', 'mysql', 'oracle'):
# MySQL has precision range 1-65 and default 10, scale default 0.
# @see https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html
# Oracle has precision range 1-38 and default 38, scale default 0.
# @see https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832
# SQL Server has range 1-38 and default 18, scale default 0.
# @see https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql
sql_type_kwargs['precision'] = 38
sql_type_kwargs['scale'] = table.aggregate(
agate.MaxPrecision(column_name))
# Avoid errors due to NO_ZERO_DATE.
# @see http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
if not isinstance(column.data_type, agate.DateTime):
sql_column_kwargs['nullable'] = table.aggregate(
agate.HasNulls(column_name))
sql_table.append_column(
make_sql_column(column_name, column, sql_type_kwargs,
sql_column_kwargs))
return sql_table
def make_sql_table(table, table_name, dialect=None, db_schema=None, constraints=True, unique_constraint=[],
connection=None, min_col_len=1, col_len_multiplier=1):
"""
Generates a SQL alchemy table from an agate table.
"""
metadata = MetaData(connection)
sql_table = Table(table_name, metadata, schema=db_schema)
SQL_TYPE_MAP[agate.Boolean] = BOOLEAN_MAP.get(dialect, BOOLEAN)
SQL_TYPE_MAP[agate.Number] = NUMBER_MAP.get(dialect, DECIMAL)
SQL_TYPE_MAP[agate.TimeDelta] = INTERVAL_MAP.get(dialect, Interval)
for column_name, column in table.columns.items():
sql_column_type = None
sql_type_kwargs = {}
sql_column_kwargs = {}
if constraints:
if isinstance(column.data_type, agate.Text) and dialect == 'mysql':
length = table.aggregate(agate.MaxLength(column_name)) * decimal.Decimal(col_len_multiplier)
if length > 21844:
# @see https://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html
sql_column_type = TEXT
else:
# If length is zero, SQLAlchemy may raise "VARCHAR requires a length on dialect mysql".
sql_type_kwargs['length'] = length if length >= min_col_len else min_col_len
# PostgreSQL and SQLite don't have scale default 0.
# @see https://www.postgresql.org/docs/9.2/static/datatype-numeric.html
# @see https://www.sqlite.org/datatype3.html
if isinstance(column.data_type, agate.Number) and dialect in ('mssql', 'mysql', 'oracle'):
# MySQL has precision range 1-65 and default 10, scale default 0.
# @see https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html
# Oracle has precision range 1-38 and default 38, scale default 0.
# @see https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832
# SQL Server has range 1-38 and default 18, scale default 0.
# @see https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql
sql_type_kwargs['precision'] = 38
sql_type_kwargs['scale'] = table.aggregate(agate.MaxPrecision(column_name))
# Avoid errors due to NO_ZERO_DATE.
# @see http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
if not isinstance(column.data_type, agate.DateTime):
sql_column_kwargs['nullable'] = table.aggregate(agate.HasNulls(column_name))
sql_table.append_column(make_sql_column(column_name, column, sql_type_kwargs, sql_column_kwargs, sql_column_type))
if unique_constraint:
sql_table.append_constraint(UniqueConstraint(*unique_constraint))
return sql_table
def convert_number_type(cls, agate_table, col_idx):
decimals = agate_table.aggregate(agate.MaxPrecision(col_idx))
return "double" if decimals else "bigint"
def convert_number_type(cls, agate_table, col_idx):
decimals = agate_table.aggregate(agate.MaxPrecision(col_idx))
return "float8" if decimals else "integer"
def convert_number_type(cls, agate_table, col_idx):
decimals = agate_table.aggregate(agate.MaxPrecision(col_idx))
return "number"
def convert_number_type(cls, agate_table, col_idx):
decimals = agate_table.aggregate(agate.MaxPrecision(col_idx))
return "DOUBLE" if decimals else "INTEGER"
def convert_number_type(
cls, agate_table: agate.Table, col_idx: int
) -> str:
# TODO: handle different precisions?
decimals = agate_table.aggregate(agate.MaxPrecision(col_idx))
return "float" if decimals else "int"
def convert_number_type(cls, agate_table: agate.Table,
col_idx: int) -> str:
decimals = agate_table.aggregate(agate.MaxPrecision(col_idx))
return "float64" if decimals else "int64"
def convert_number_type(cls, agate_table, col_idx):
decimals = agate_table.aggregate(agate.MaxPrecision(col_idx))
return "numeric(18,{})".format(decimals) if decimals else "integer"