def test_sql_storage_mysql_constraints(mysql_url):
dialect = SqlDialect(prefix="prefix_")
source = Package("data/storage/constraints.json")
storage = source.to_sql(mysql_url, dialect=dialect)
target = Package.from_sql(mysql_url, dialect=dialect)
# Assert metadata
assert target.get_resource("constraints").schema == {
"fields": [
{"name": "required", "type": "string", "constraints": {"required": True}},
{"name": "minLength", "type": "string"}, # constraint removal
{"name": "maxLength", "type": "string"}, # constraint removal
{"name": "pattern", "type": "string"}, # constraint removal
{"name": "enum", "type": "string"}, # constraint removal
{"name": "minimum", "type": "integer"}, # constraint removal
{"name": "maximum", "type": "integer"}, # constraint removal
],
}
# Assert data
assert target.get_resource("constraints").read_rows() == [
{
"required": "passing",
"minLength": "passing",
"maxLength": "passing",
"pattern": "passing",
"enum": "passing",
"minimum": 5,
"maximum": 5,
},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_sql_parser(database_url):
dialect = SqlDialect(table="table")
with Resource(database_url, dialect=dialect) as resource:
assert resource.schema == {
"fields": [
{
"constraints": {
"required": True
},
"name": "id",
"type": "integer"
},
{
"name": "name",
"type": "string"
},
],
"primaryKey": ["id"],
}
assert resource.header == ["id", "name"]
assert resource.read_rows() == [
{
"id": 1,
"name": "english"
},
{
"id": 2,
"name": "中国人"
},
]
def test_table_sql_write_timezone(sqlite_url):
source = "data/timezone.csv"
dialect = SqlDialect(table="timezone")
with Table(source) as table:
table.write(sqlite_url, dialect=dialect)
with Table(sqlite_url, dialect=dialect) as table:
assert table.header == ["datetime", "time"]
assert table.read_rows() == [
{
"datetime": datetime.datetime(2020, 1, 1, 15),
"time": datetime.time(15)
},
{
"datetime": datetime.datetime(2020, 1, 1, 15),
"time": datetime.time(15)
},
{
"datetime": datetime.datetime(2020, 1, 1, 15),
"time": datetime.time(15)
},
{
"datetime": datetime.datetime(2020, 1, 1, 15),
"time": datetime.time(15)
},
]
def test_sql_parser_where(database_url):
dialect = SqlDialect(table="table", where="name = '中国人'")
with Resource(database_url, dialect=dialect) as resource:
assert resource.header == ["id", "name"]
assert resource.read_rows() == [
{"id": 2, "name": "中国人"},
]
def test_sql_parser_order_by_desc(database_url):
dialect = SqlDialect(table="table", order_by="id desc")
with Resource(database_url, dialect=dialect) as resource:
assert resource.header == ["id", "name"]
assert resource.read_rows() == [
{"id": 2, "name": "中国人"},
{"id": 1, "name": "english"},
]
def test_program_extract_dialect_table_option_sql(database_url):
table = "fruits"
result = runner.invoke(program, f"extract {database_url} --table {table} --json")
if IS_UNIX:
assert result.exit_code == 0
dialect = SqlDialect(table=table)
with Resource(database_url, dialect=dialect) as resource:
assert json.loads(result.stdout) == extract(resource)
def test_sql_storage_postgresql_integrity_different_order_issue_957(postgresql_url):
dialect = SqlDialect(prefix="prefix_")
source = Package("data/storage/integrity.json")
source.add_resource(source.remove_resource("integrity_main"))
storage = source.to_sql(postgresql_url, dialect=dialect)
target = Package.from_sql(postgresql_url, dialect=dialect)
assert len(target.resources) == 2
storage.delete_package(target.resource_names)
def test_sql_storage_dialect_basepath_issue_964(sqlite_url):
dialect = SqlDialect(table="test_table", basepath="data")
with Resource(path="sqlite:///sqlite.db", dialect=dialect) as resource:
assert resource.read_rows() == [
{"id": 1, "name": "foo"},
{"id": 2, "name": "bar"},
{"id": 3, "name": "baz"},
]
def test_table_sql_write(database_url):
source = "data/table.csv"
dialect = SqlDialect(table="name", order_by="id")
with Table(source) as table:
table.write(database_url, dialect=dialect)
with Table(database_url, dialect=dialect) as table:
assert table.header == ["id", "name"]
assert table.read_data() == [[1, "english"], [2, "中国人"]]
def test_sql_parser_write(database_url):
source = Resource("data/table.csv")
target = source.write(database_url, dialect=SqlDialect(table="name", order_by="id"))
with target:
assert target.header == ["id", "name"]
assert target.read_rows() == [
{"id": 1, "name": "english"},
{"id": 2, "name": "中国人"},
]
def test_sql_parser_headers_false(database_url):
dialect = SqlDialect(table="table")
layout = Layout(header=False)
with Resource(database_url, dialect=dialect, layout=layout) as resource:
assert resource.header == ["id", "name"]
assert resource.read_rows() == [
{"id": None, "name": "name"},
{"id": 1, "name": "english"},
{"id": 2, "name": "中国人"},
]
def test_sql_storage_sqlite_integer_enum_issue_776(sqlite_url):
dialect = SqlDialect(table="table")
source = Resource(path="data/table.csv")
source.infer()
source.schema.get_field("id").constraints["enum"] = [1, 2]
target = source.write(sqlite_url, dialect=dialect)
assert target.read_rows() == [
{"id": 1, "name": "english"},
{"id": 2, "name": "中国人"},
]
def test_sql_parser_write_timezone(sqlite_url):
source = Resource("data/timezone.csv")
target = source.write(sqlite_url, dialect=SqlDialect(table="timezone"))
with target:
assert target.header == ["datetime", "time"]
assert target.read_rows() == [
{"datetime": datetime.datetime(2020, 1, 1, 15), "time": datetime.time(15)},
{"datetime": datetime.datetime(2020, 1, 1, 15), "time": datetime.time(15)},
{"datetime": datetime.datetime(2020, 1, 1, 15), "time": datetime.time(15)},
{"datetime": datetime.datetime(2020, 1, 1, 15), "time": datetime.time(15)},
]
def test_package_to_zip_resource_sql(tmpdir, database_url):
path = os.path.join(tmpdir, "package.zip")
dialect = SqlDialect(table="table")
source = Package(resources=[Resource(database_url, name="table", dialect=dialect)])
source.to_zip(path)
target = Package.from_zip(path)
assert target.get_resource("table").path == database_url
assert target.get_resource("table").read_rows() == [
{"id": 1, "name": "english"},
{"id": 2, "name": "中国人"},
]
def test_sql_storage_mysql_integrity(mysql_url):
dialect = SqlDialect(prefix="prefix_")
source = Package("data/storage/integrity.json")
storage = source.to_sql(mysql_url, dialect=dialect)
target = Package.from_sql(mysql_url, dialect=dialect)
# Assert metadata (main)
assert target.get_resource("integrity_main").schema == {
"fields": [
# added required
{"name": "id", "type": "integer", "constraints": {"required": True}},
{"name": "parent", "type": "integer"},
{"name": "description", "type": "string"},
],
"primaryKey": ["id"],
"foreignKeys": [
{"fields": ["parent"], "reference": {"resource": "", "fields": ["id"]}}
],
}
# Assert metadata (link)
assert target.get_resource("integrity_link").schema == {
"fields": [
# added required
{"name": "main_id", "type": "integer", "constraints": {"required": True}},
# added required; removed unique
{"name": "some_id", "type": "integer", "constraints": {"required": True}},
# removed unique
{"name": "description", "type": "string"},
],
"primaryKey": ["main_id", "some_id"],
"foreignKeys": [
{
"fields": ["main_id"],
"reference": {"resource": "integrity_main", "fields": ["id"]},
}
],
}
# Assert data (main)
assert target.get_resource("integrity_main").read_rows() == [
{"id": 1, "parent": None, "description": "english"},
{"id": 2, "parent": 1, "description": "中国人"},
]
# Assert data (link)
assert target.get_resource("integrity_link").read_rows() == [
{"main_id": 1, "some_id": 1, "description": "note1"},
{"main_id": 2, "some_id": 2, "description": "note2"},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_sql_parser_write_string_pk_issue_777_sqlite(sqlite_url):
source = Resource("data/table.csv")
source.infer()
source.schema.primary_key = ["name"]
target = source.write(sqlite_url, dialect=SqlDialect(table="name"))
with target:
assert target.schema.primary_key == ["name"]
assert target.header == ["id", "name"]
assert target.read_rows() == [
{"id": 1, "name": "english"},
{"id": 2, "name": "中国人"},
]
def test_sql_storage_mysql_types(mysql_url):
dialect = SqlDialect(prefix="prefix_")
source = Package("data/storage/types.json")
storage = source.to_sql(mysql_url, dialect=dialect)
target = Package.from_sql(mysql_url, dialect=dialect)
# Assert metadata
assert target.get_resource("types").schema == {
"fields": [
{"name": "any", "type": "string"}, # type fallback
{"name": "array", "type": "string"}, # type fallback
{"name": "boolean", "type": "integer"}, # type downgrade
{"name": "date", "type": "date"},
{"name": "date_year", "type": "date"}, # format removal
{"name": "datetime", "type": "datetime"},
{"name": "duration", "type": "string"}, # type fallback
{"name": "geojson", "type": "string"}, # type fallback
{"name": "geopoint", "type": "string"}, # type fallback
{"name": "integer", "type": "integer"},
{"name": "number", "type": "number"},
{"name": "object", "type": "string"}, # type fallback
{"name": "string", "type": "string"},
{"name": "time", "type": "time"},
{"name": "year", "type": "integer"}, # type downgrade
{"name": "yearmonth", "type": "string"}, # type fallback
],
}
# Assert data
assert target.get_resource("types").read_rows() == [
{
"any": "中国人",
"array": '["Mike", "John"]',
"boolean": True,
"date": datetime.date(2015, 1, 1),
"date_year": datetime.date(2015, 1, 1),
"datetime": datetime.datetime(2015, 1, 1, 3, 0),
"duration": "P1Y1M",
"geojson": '{"type": "Point", "coordinates": [33, 33.33]}',
"geopoint": "30,70",
"integer": 1,
"number": 7,
"object": '{"chars": 560}',
"string": "english",
"time": datetime.time(3, 0),
"year": 2015,
"yearmonth": "2015-01",
},
]
# Cleanup storage
storage.delete_package(target.resource_names)
def test_sql_parser_write_string_pk_issue_777_mysql(mysql_url):
source = Resource("data/table.csv")
source.infer()
source.schema.primary_key = ["name"]
source.schema.get_field("name").constraints["maxLength"] = 100
target = source.write(mysql_url, dialect=SqlDialect(table="name"))
with target:
assert target.schema.primary_key == ["name"]
assert target.header == ["id", "name"]
assert target.read_rows() == [
{"id": 1, "name": "english"},
{"id": 2, "name": "中国人"},
]
def test_table_sql(database_url):
dialect = SqlDialect(table="data")
with Table(database_url, dialect=dialect) as table:
assert table.schema == {
"fields": [
{
"constraints": {
"required": True
},
"name": "id",
"type": "integer"
},
{
"name": "name",
"type": "string"
},
],
"primaryKey": ["id"],
}
assert table.header == ["id", "name"]
assert table.read_data() == [[1, "english"], [2, "中国人"]]
def test_sql_storage_mysql_comment_support(mysql_url):
dialect = SqlDialect(table="table")
# Write
source = Resource(path="data/table.csv")
source.infer()
source.schema.get_field("id").description = "integer field"
source.schema.get_field("name").description = "string field"
source.write(mysql_url, dialect=dialect)
# Read
target = Resource(mysql_url, dialect=dialect)
with target:
assert target.schema == {
"fields": [
{"name": "id", "type": "integer", "description": "integer field"},
{"name": "name", "type": "string", "description": "string field"},
]
}
assert target.read_rows() == [
{"id": 1, "name": "english"},
{"id": 2, "name": "中国人"},
]
def test_sql_parser_order_by(database_url):
dialect = SqlDialect(table="table", order_by="id")
with Table(database_url, dialect=dialect) as table:
assert table.header == ["id", "name"]
assert table.read_data() == [[1, "english"], [2, "中国人"]]
def test_table_sql_headers_false(database_url):
dialect = SqlDialect(table="data")
with Table(database_url, dialect=dialect, headers=False) as table:
assert table.header == []
assert table.read_data() == [["id", "name"], [1, "english"],
[2, "中国人"]]
def test_table_sql_order_by_desc(database_url):
dialect = SqlDialect(table="data", order_by="id desc")
with Table(database_url, dialect=dialect) as table:
assert table.header == ["id", "name"]
assert table.read_data() == [[2, "中国人"], [1, "english"]]
def test_table_format_sql(database_url):
dialect = SqlDialect(table="data")
with Table(database_url, dialect=dialect) as table:
assert table.header == ["id", "name"]
assert table.read_data() == [[1, "english"], [2, "中国人"]]
def test_sql_parser_describe_to_yaml_issue_821(database_url):
dialect = SqlDialect(table="table")
resource = Resource(database_url, dialect=dialect)
resource.infer()
assert resource.to_yaml()