ENH: change to_sql default from Text to UnicodeText

[gordthompson]

Is your feature request related to a problem?

Yes. Databases that differentiate between Unicode and SBCS columns (notably Microsoft SQL Server nvarchar vs. varchar) can fail simple round-trips when using to_sql to write columns with Unicode strings.

Describe the solution you'd like

Change the default column dtype from Text ...

pandas/pandas/io/sql.py

Line 1080 in a9cb64a

return Text

... to UnicodeText.

API breaking implications

None that I can see.

Describe alternatives you've considered

An alternative would be to modify the behaviour of Text in SQLAlchemy's mssql dialect, but SQLA already has UnicodeText and it solves the problem.

Additional context

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("mssql+pyodbc://@mssqlLocal")

df = pd.DataFrame(
    [("English", "CHEERS!"), ("Greek", "ΟΠΑ!")], columns=["language", "toast"]
)
df.to_sql("pd_test", engine, index=False, if_exists="replace")
print(pd.read_sql_query("SELECT * FROM pd_test", engine))
"""console output:
  language    toast
0  English  CHEERS!
1    Greek     ???!
"""

[mathurk1]

able to create this in the master branch as well,

def main():

    df = pd.DataFrame(
    [("English", "CHEERS!"), ("Greek", "ΟΠΑ!")], columns=["language", "toast"]
    )

    df.to_sql("pd_test", db_engine(), index=False, if_exists="replace")

    print(pd.__version__)
    print(pd.read_sql_query("SELECT * FROM pd_test", db_engine()))


if __name__ == "__main__":
    main()

produces

1.1.0.dev0+2073.g280efbfcc
  language    toast
0  English  CHEERS!
1    Greek     ???!

[mathurk1]

it works fine when the database is a postgres -

1.1.0.dev0+2073.g280efbfcc
  language    toast
0  English  CHEERS!
1    Greek     ΟΠΑ!

the table created in postgres is below:

create table pd_test
(
	language text,
	toast text
);

[gordthompson]

Yes, and this works with both MSSQL and PostgreSQL:

df = pd.DataFrame(
    [("English", "CHEERS!"), ("Greek", "ΟΠΑ!")], columns=["language", "toast"]
)
df.to_sql(
    "pd_test",
    engine,
    index=False,
    if_exists="replace",
    dtype={"toast": sa.UnicodeText},
)
print(pd.read_sql_query("SELECT * FROM pd_test", engine))
"""console output:
  language    toast
0  English  CHEERS!
1    Greek     ΟΠΑ!
"""

My request is to make that the default so we don't have to specify it every time.