Offline migration special characters are not translated properly.

[le0nce]

Hi, I am facing the issue, that special chars are not properly translated from a head to a offline sql migration skript.
Here is a simplified version of the problem.
I am using python==3.8.5, alembic==1.13.1

Sql version, that needs to be translated:
---b47012d8c2bd_init_database.sql

  CREATE TABLE [dbo].[attributes](
                  [id] [int] IDENTITY(1,1) NOT NULL,
                  [name] [nvarchar](255) NOT NULL,
                  [display_name] [nvarchar](255) NULL,
                  [type] [nvarchar](255) NULL,
                  [default_values] [nvarchar](255) NULL,
                  [is_required] [bit] NOT NULL,
                  [validation_regex] [nvarchar](255) NULL)
  
  INSERT [dbo].[attributes] ([name], [display_name], [type], [default_values], [is_required], [validation_regex]) VALUES (N'datum_ausserkraft', N'Datum Außerkraft', N'date', NULL, 1, NULL)

The migration script:
---b47012d8c2bd_init_database.py

  def upgrade() -> None:
      dir_path = os.path.dirname(os.path.realpath(__file__))
      sql_file_path = os.path.join(dir_path, "b47012d8c2db_init_database.sql")
      with open(sql_file_path, 'r') as file:
          sql_content = file.read()
          op.execute(sql_content)

---

After running the command "alembic upgrade heads --sql > initial_migration_scripts.sql" the generated offline migration skript looks like this

---initial_migration_scripts.sql

  BEGIN TRANSACTION;
  
  CREATE TABLE alembic_version (
      version_num VARCHAR(32) NOT NULL, 
      CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
  );
  
  GO
  
  -- Running upgrade  -> b47012d8c2db
  
  CREATE TABLE [dbo].[attributes](
                  [id] [int] IDENTITY(1,1) NOT NULL,
                  [name] [nvarchar](255) NOT NULL,
                  [display_name] [nvarchar](255) NULL,
                  [type] [nvarchar](255) NULL,
                  [default_values] [nvarchar](255) NULL,
                  [is_required] [bit] NOT NULL,
                  [validation_regex] [nvarchar](255) NULL)
  
  INSERT [dbo].[attributes] ([name], [display_name], [type], [default_values], [is_required], [validation_regex]) VALUES (N'datum_ausserkraft', N'Datum Außerkraft', N'date', NULL, 1, NULL);
  
  GO
  
  INSERT INTO alembic_version (version_num) OUTPUT inserted.version_num VALUES ('b47012d8c2db');
  
  GO
  
  COMMIT;
  
  GO
  
  alembic.runtime.migration: Finished

---

As you can see "Datum Außerkraft" was translated to "Datum Au├ƒerkraft". I hope you have some information on how to fix this issue.
Kind regards,
Leon

[zzzeek]

hi -

it would seem you have some encoding issue somewhere, your script as written works fine on this end, sample diff

-  INSERT [dbo].[attributes] ([name], [display_name], [type], [default_values], [is_required], [validation_regex]) VALUES (N'datum_ausserkraft', N'Datum Außerkraft', N'date', NULL, 1, NULL);
-
-INSERT INTO alembic_version (version_num) VALUES ('069aa19fda58') RETURNING alembic_version.version_num;
-
-COMMIT;
-
+  INSERT [dbo].[attributes] ([name], [display_name], [type], [default_values], [is_required], [validation_regex]) VALUES (N'datum_ausserkraft', N'Datum Außerkraft', N'date', NULL, 1, NULL)

make sure the encoding of your original .sql file is what you expect, that the encoding used when you open() the file matches, and that the encoding used overall matches the default encoding of your Python interpreter. sys.stdout is used by default which has some encoding parameters https://docs.python.org/3/library/sys.html#sys.stdout . you can also supply your own output buffer in your env.py so you should have full visibility / control into the movement of the text.

Shorter answer I'd triple check your file is encoded in utf-8 and that the platform encoding in use is also utf-8.

[le0nce]

I solved the issue with modifing the run_migrations_offline in env.py:

Added buffer:

    buffer = io.StringIO()

    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
        output_buffer=buffer
    ) 

Wrote output manually

    output = buffer.getvalue()
    print(output)

    with open(f"sql_scripts\\{revision_id}_deployment.sql", "w", encoding="utf-8") as f:
        f.write(output)