Add country names in full to database, instead of two letter ISO codes (+i18n)

[spwoodcock]

Is your feature request related to a problem? Please describe.

• The data source we use has two letter ISO codes for countries.
• It would be more user friendly if our query output the actual country long name.

So names like Kathmandu,NP would become Kathmandu,Nepal.

Do you think this is a good idea @emirfabio ?

Describe the solution you'd like

• Option 1: insert the names already converted (may require manipulating source data, as we use a binary COPY.
• Option 2: convert to the long name on query, using a dict mapping.

Additional context

Mapping:

countries = [
    ("AF", "Afghanistan"),
    ("AX", "\xc5land Islands"),
    ("AL", "Albania"),
    ("DZ", "Algeria"),
    ("AS", "American Samoa"),
    ("AD", "Andorra"),
    ("AO", "Angola"),
    ("AI", "Anguilla"),
    ("AQ", "Antarctica"),
    ("AG", "Antigua and Barbuda"),
    ("AR", "Argentina"),
    ("AM", "Armenia"),
    ("AW", "Aruba"),
    ("AU", "Australia"),
    ("AT", "Austria"),
    ("AZ", "Azerbaijan"),
    ("BS", "Bahamas"),
    ("BH", "Bahrain"),
    ("BD", "Bangladesh"),
    ("BB", "Barbados"),
    ("BY", "Belarus"),
    ("BE", "Belgium"),
    ("BZ", "Belize"),
    ("BJ", "Benin"),
    ("BM", "Bermuda"),
    ("BT", "Bhutan"),
    ("BO", "Bolivia, Plurinational State of"),
    ("BQ", "Bonaire, Sint Eustatius and Saba"),
    ("BA", "Bosnia and Herzegovina"),
    ("BW", "Botswana"),
    ("BV", "Bouvet Island"),
    ("BR", "Brazil"),
    ("IO", "British Indian Ocean Territory"),
    ("BN", "Brunei Darussalam"),
    ("BG", "Bulgaria"),
    ("BF", "Burkina Faso"),
    ("BI", "Burundi"),
    ("KH", "Cambodia"),
    ("CM", "Cameroon"),
    ("CA", "Canada"),
    ("CV", "Cape Verde"),
    ("KY", "Cayman Islands"),
    ("CF", "Central African Republic"),
    ("TD", "Chad"),
    ("CL", "Chile"),
    ("CN", "China"),
    ("CX", "Christmas Island"),
    ("CC", "Cocos (Keeling Islands)"),
    ("CO", "Colombia"),
    ("KM", "Comoros"),
    ("CG", "Congo"),
    ("CD", "Congo, The Democratic Republic of the"),
    ("CK", "Cook Islands"),
    ("CR", "Costa Rica"),
    ("CI", "C\xf4te D'ivoire"),
    ("HR", "Croatia"),
    ("CU", "Cuba"),
    ("CW", "Cura\xe7ao"),
    ("CY", "Cyprus"),
    ("CZ", "Czech Republic"),
    ("DK", "Denmark"),
    ("DJ", "Djibouti"),
    ("DM", "Dominica"),
    ("DO", "Dominican Republic"),
    ("EC", "Ecuador"),
    ("EG", "Egypt"),
    ("SV", "El Salvador"),
    ("GQ", "Equatorial Guinea"),
    ("ER", "Eritrea"),
    ("EE", "Estonia"),
    ("ET", "Ethiopia"),
    ("FK", "Falkland Islands (Malvinas)"),
    ("FO", "Faroe Islands"),
    ("FJ", "Fiji"),
    ("FI", "Finland"),
    ("FR", "France"),
    ("GF", "French Guiana"),
    ("PF", "French Polynesia"),
    ("TF", "French Southern Territories"),
    ("GA", "Gabon"),
    ("GM", "Gambia"),
    ("GE", "Georgia"),
    ("DE", "Germany"),
    ("GH", "Ghana"),
    ("GI", "Gibraltar"),
    ("GR", "Greece"),
    ("GL", "Greenland"),
    ("GD", "Grenada"),
    ("GP", "Guadeloupe"),
    ("GU", "Guam"),
    ("GT", "Guatemala"),
    ("GG", "Guernsey"),
    ("GN", "Guinea"),
    ("GW", "Guinea-bissau"),
    ("GY", "Guyana"),
    ("HT", "Haiti"),
    ("HM", "Heard Island and McDonald Islands"),
    ("VA", "Holy See (Vatican City State)"),
    ("HN", "Honduras"),
    ("HK", "Hong Kong"),
    ("HU", "Hungary"),
    ("IS", "Iceland"),
    ("IN", "India"),
    ("ID", "Indonesia"),
    ("IR", "Iran, Islamic Republic of"),
    ("IQ", "Iraq"),
    ("IE", "Ireland"),
    ("IM", "Isle of Man"),
    ("IL", "Israel"),
    ("IT", "Italy"),
    ("JM", "Jamaica"),
    ("JP", "Japan"),
    ("JE", "Jersey"),
    ("JO", "Jordan"),
    ("KZ", "Kazakhstan"),
    ("KE", "Kenya"),
    ("KI", "Kiribati"),
    ("KP", "Korea, Democratic People's Republic of"),
    ("KR", "Korea, Republic of"),
    ("KW", "Kuwait"),
    ("KG", "Kyrgyzstan"),
    ("LA", "Lao People's Democratic Republic"),
    ("LV", "Latvia"),
    ("LB", "Lebanon"),
    ("LS", "Lesotho"),
    ("LR", "Liberia"),
    ("LY", "Libya"),
    ("LI", "Liechtenstein"),
    ("LT", "Lithuania"),
    ("LU", "Luxembourg"),
    ("MO", "Macao"),
    ("MK", "Macedonia, The Former Yugoslav Republic of"),
    ("MG", "Madagascar"),
    ("MW", "Malawi"),
    ("MY", "Malaysia"),
    ("MV", "Maldives"),
    ("ML", "Mali"),
    ("MT", "Malta"),
    ("MH", "Marshall Islands"),
    ("MQ", "Martinique"),
    ("MR", "Mauritania"),
    ("MU", "Mauritius"),
    ("YT", "Mayotte"),
    ("MX", "Mexico"),
    ("FM", "Micronesia, Federated States of"),
    ("MD", "Moldova, Republic of"),
    ("MC", "Monaco"),
    ("MN", "Mongolia"),
    ("ME", "Montenegro"),
    ("MS", "Montserrat"),
    ("MA", "Morocco"),
    ("MZ", "Mozambique"),
    ("MM", "Myanmar"),
    ("NA", "Namibia"),
    ("NR", "Nauru"),
    ("NP", "Nepal"),
    ("NL", "Netherlands"),
    ("NC", "New Caledonia"),
    ("NZ", "New Zealand"),
    ("NI", "Nicaragua"),
    ("NE", "Niger"),
    ("NG", "Nigeria"),
    ("NU", "Niue"),
    ("NF", "Norfolk Island"),
    ("MP", "Northern Mariana Islands"),
    ("NO", "Norway"),
    ("OM", "Oman"),
    ("PK", "Pakistan"),
    ("PW", "Palau"),
    ("PS", "Palestinian Territory, Occupied"),
    ("PA", "Panama"),
    ("PG", "Papua New Guinea"),
    ("PY", "Paraguay"),
    ("PE", "Peru"),
    ("PH", "Philippines"),
    ("PN", "Pitcairn"),
    ("PL", "Poland"),
    ("PT", "Portugal"),
    ("PR", "Puerto Rico"),
    ("QA", "Qatar"),
    ("RE", "R\xe9union"),
    ("RO", "Romania"),
    ("RU", "Russian Federation"),
    ("RW", "Rwanda"),
    ("BL", "Saint Barth\xe9lemy"),
    ("SH", "Saint Helena, Ascension and Tristan Da Cunha"),
    ("KN", "Saint Kitts and Nevis"),
    ("LC", "Saint Lucia"),
    ("MF", "Saint Martin (French Part)"),
    ("PM", "Saint Pierre and Miquelon"),
    ("VC", "Saint Vincent and the Grenadines"),
    ("WS", "Samoa"),
    ("SM", "San Marino"),
    ("ST", "Sao Tome and Principe"),
    ("SA", "Saudi Arabia"),
    ("SN", "Senegal"),
    ("RS", "Serbia"),
    ("SC", "Seychelles"),
    ("SL", "Sierra Leone"),
    ("SG", "Singapore"),
    ("SX", "Sint Maarten (Dutch Part)"),
    ("SK", "Slovakia"),
    ("SI", "Slovenia"),
    ("SB", "Solomon Islands"),
    ("SO", "Somalia"),
    ("ZA", "South Africa"),
    ("GS", "South Georgia and the South Sandwich Islands"),
    ("SS", "South Sudan"),
    ("ES", "Spain"),
    ("LK", "Sri Lanka"),
    ("SD", "Sudan"),
    ("SR", "Suriname"),
    ("SJ", "Svalbard and Jan Mayen"),
    ("SZ", "Swaziland"),
    ("SE", "Sweden"),
    ("CH", "Switzerland"),
    ("SY", "Syrian Arab Republic"),
    ("TW", "Taiwan, Province of China"),
    ("TJ", "Tajikistan"),
    ("TZ", "Tanzania, United Republic of"),
    ("TH", "Thailand"),
    ("TL", "Timor-leste"),
    ("TG", "Togo"),
    ("TK", "Tokelau"),
    ("TO", "Tonga"),
    ("TT", "Trinidad and Tobago"),
    ("TN", "Tunisia"),
    ("TR", "Turkey"),
    ("TM", "Turkmenistan"),
    ("TC", "Turks and Caicos Islands"),
    ("TV", "Tuvalu"),
    ("UG", "Uganda"),
    ("UA", "Ukraine"),
    ("AE", "United Arab Emirates"),
    ("GB", "United Kingdom"),
    ("US", "United States"),
    ("UM", "United States Minor Outlying Islands"),
    ("UY", "Uruguay"),
    ("UZ", "Uzbekistan"),
    ("VU", "Vanuatu"),
    ("VE", "Venezuela, Bolivarian Republic of"),
    ("VN", "Viet Nam"),
    ("VG", "Virgin Islands, British"),
    ("VI", "Virgin Islands, U.S."),
    ("WF", "Wallis and Futuna"),
    ("EH", "Western Sahara"),
    ("YE", "Yemen"),
    ("ZM", "Zambia"),
    ("ZW", "Zimbabwe"),
]

[emirfabio]

yesss, definitely a good idea.
I would keep using the db for consistency.

We could add another table for the country code to country name mapping, this would be nice for i18n purposes, what do you think @spwoodcock ?
Or directly add the column to the main table if i18n is not a necessity

[spwoodcock]

yesss, definitely a good idea. I would keep using the db for consistency.

We could add another table for the country code to country name mapping, this would be nice for i18n purposes, what do you think @spwoodcock ? Or directly add the column to the main table if i18n is not a necessity

Love this idea to deal with i18n too!

It's not strictly essential, but I'm sure it would be a very nice / unique point of this lib 😄

[stephanGarland]

IMO, a separate lookup table is the way to go to maintain referential integrity and minimize space consumption. A table for ISO3166-1 alpha-2 at a minimum, optionally also with alpha-3.

Would you also like foreign key constraints? Happy to take this on.

Tangentially, I modified the import script to a. use csv instead of shelling out to awk b. use Postgres' COPY ... WHERE syntax for filtering, rather than deleting other rows afterwards. Would you like me to have separate PRs for those (if you're interested), or combined into one?

[spwoodcock]

Hey @stephanGarland thanks so much for the info and offer to help!

I agree with you on both alpha-3 iso codes if possible, plus foreign key constraints.

It would be awesome if you want to optimise the import script further too 😄

Typically 2 PRs would be good to separate review / discussions, but honestly, if it's easier to just bundle all into one, I have no complaints there!

[stephanGarland]

@spwoodcock I opted to split the PRs. First one is just the import script refactor; I'll work on the DB part later.

[stephanGarland]

Before I make sweeping changes, I figured I'd ask.

Do you have interest in defining the schema as objects in Python, and generating the SQL from that? Rough draft example:

@dataclass(frozen=True)
class Constraint:
    name: str
    type: ConstraintType
    columns: list[Column] = field(default_factory=list)
    foreign_key: ForeignKey | None = None
    check_expression: str | None = None


@dataclass(frozen=True)
class Column:
    name: str
    data_type: DatabaseType
    is_nullable: bool = False
    default: str | None = None
    is_primary_key: bool = False
    is_unique: bool = False
    constraint: Constraint | None = None


@dataclass(frozen=True)
class ForeignKey:
    column: str
    table: str
    references_column: str
    references_table: str
    on_delete: str = "RESTRICT"
    on_update: str = "RESTRICT"


@dataclass(frozen=True)
class Index:
    name: str
    columns: list[Column]
    unique: bool = False
    function: str | None = None
    where_clause: str | None = None

It's overkill for a one-off, but if you were using similar constructs elsewhere, it might make sense. There are also libraries like Pydantic if you didn't want a DIY approach.

I'm fine with having raw SQL if that's what you'd prefer, but wanted to offer both as options.

[spwoodcock]

Thanks for prompting this - it could be a good idea to use dataclasses to define some structs used throughout the code 😄

As for pydantic - it's a library that I love and use a lot in upstream applications, but I would really like to keep this installable package to a bare minimum of dependencies - psycopg only should work for our needs!

I'm not sure there is much benefit on creating the tables using the dataclasses vs raw SQL - if anything it's just an extra abstraction - but I could be convinced otherwise if you can see a good reason!

A good way to use dataclasses would be in psycopg row factories.

We could then very nicely do something like:

from psycopg.rows import class_row

async def get_my_data() -> SomeDefinedDataclass:
    async with db.cursor(row_factory=class_row(SomeDefinedDataclass)) as cur:
        await cur.execute(
            sql,
            {"some_param": some_param},
        )
        return await cur.fetchall()

allowing us to cast the database return into a structured object.

What do you think?

[stephanGarland]

but I would really like to keep this installable package to a bare minimum of dependencies - psycopg only should work for our needs!

Absolutely, I fully support and applaud that mentality, and try to do it myself wherever possible, just wanted to give more options. I have no issues with just writing the raw SQL – my main thought was that if there are related projects (I assume they're related; I haven't spent much time digging around the org's repos yet, just stumbled upon this one from an HN comment) like fmtm-splitter, there could be a centralized source of truth for DB schemata, or if not that, a template of sorts for declaring it, such that all projects would look the same to reviewers.

Re: casting the DB return, yes, that's always a possibility, though you can also do the same with namedtuple._make, albeit it's lighter-weight and is generally less flexible than others. The other thing to be careful of with anything like that is to not fall into the trap of requiring that read queries be SELECT *. It probably doesn't matter as much with small, local tables like this, but for anything centralized (or just larger tables) it can start to have negative impacts on performance.

So it sounds for this specific ask, I'll stick to the current format. No problem!