This user's question is also on bitbucket where I've summarized what is likely going on, so we can avoid doing the same work twice here: https://bitbucket.org/zzzeek/sqlalchemy/issues/4085/warning-1366-incorrect-string-value#comment-47394537
On Wed, Aug 22, 2018 at 6:20 AM, Simon King <si...@simonking.org.uk> wrote: > According to https://dev.mysql.com/doc/refman/5.7/en/variables-table.html, > VARIABLE_VALUE is a column in the INFORMATION_SCHEMA GLOBAL_VARIABLES and > SESSION_VARIABLES tables. > > You could use the python warnings filter to turn these warnings into > exceptions: > > import warnings > warnings.simplefilter("error") > > ...which will give you an exception and a stack trace, so you can see > where these warnings are coming from. > > Simon > > On Wed, Aug 22, 2018 at 10:37 AM <imad.youbi.idri...@gmail.com> wrote: > >> Sorry for my last messy message, here's a better version. >> >> That's a good question actually. If you follow my Stackoverflow Post >> you'll see I've updated it with the following information : >> >> >> The dataframe that is persisted in the database (See attached image): >> >> >> [image: DataFrame.JPG] >> >> So I actually have no idea wherethe column "VARIABLE_VALUE" is coming >> from. >> >> >> On Wednesday, August 22, 2018 at 10:37:30 AM UTC+2, Simon King wrote: >>> >>> I've never used Pandas, so this may not make any sense, but where does >>> the column "VARIABLE_VALUE" come from? Is it a column in your >>> dataframe? >>> >>> Simon >>> >>> On Wed, Aug 22, 2018 at 8:52 AM <imad.youb...@gmail.com> wrote: >>> > >>> > I get the following warnings, when trying to save a simple dataframe >>> to mysql.: >>> > >>> > > C:\...\anaconda3\lib\site-packages\pymysql\cursors.py:170: Warning: >>> (1366, "Incorrect string value: '\\x92\\xE9t\\xE9)' for column >>> 'VARIABLE_VALUE' at row 518") >>> > result = self._query(query) >>> > >>> > And >>> > > C:\...anaconda3\lib\site-packages\pymysql\cursors.py:170: Warning: >>> > > (3719, "'utf8' is currently an alias for the character set UTF8MB3, >>> > > but will be an alias for UTF8MB4 in a future release. Please >>> consider >>> > > using UTF8MB4 in order to be unambiguous.") result = >>> > > self._query(query) >>> > >>> > Environment info : I use Mysql8, python3.6 (pymysql 0.9.2, sqlalchemy >>> 1.2.1) >>> > >>> > I visited posts like the one linked bellow, none of which seem to give >>> a solution as to how to avoid this warning. >>> > >>> > - [MySQL “incorrect string value” error when save unicode string in >>> Django][1] -> Indication is to use UTF8 >>> > >>> > >>> > N.B : The Collation in the table within mysql doesn't seem to be set >>> to the one I specified in the `create_db` function within the `Connection` >>> class. >>> > >>> > The executable code: >>> > >>> > import DataEngine.db.Connection as connection >>> > import random >>> > import pandas as pd >>> > >>> > if __name__ == "__main__": >>> > conn = connection.Connection(host="host_name", port="3306", >>> user="username", password="password") >>> > conn.create_db("raw_data") >>> > conn.establish("raw_data") >>> > l1 = [] >>> > for i in range(100): >>> > l_nested = [] >>> > for j in range(10): >>> > l_nested.append(random.randint(0, 100)) >>> > l1.append(l_nested) >>> > df = pd.DataFrame(l1) >>> > >>> > conn.save(df, "random_df") >>> > # df2 = conn.retrieve("random_df") >>> > >>> > My `Connection class` >>> > >>> > import sqlalchemy >>> > import pymysql >>> > import pandas as pd >>> > >>> > >>> > class Connection: >>> > def __init__(self: object, host: str, port: str, user: str, >>> password: str): >>> > self.host = host >>> > self.port = port >>> > self.user = user >>> > self.password = password >>> > self.conn = None >>> > >>> > def create_db(self: object, db_name: str, charset: str = >>> "utf8mb4", collate:str ="utf8mb4_unicode_ci",drop_if_exists: bool = >>> True): >>> > c = pymysql.connect(host=self.host, user=self.user, >>> password=self.password) >>> > if drop_if_exists: >>> > c.cursor().execute("DROP DATABASE IF EXISTS " + >>> db_name) >>> > c.cursor().execute("CREATE DATABASE " + db_name + " >>> CHARACTER SET=" + charset + " COLLATE=" + collate) >>> > c.close() >>> > print("Database %s created with a %s charset" % (db_name, >>> charset)) >>> > >>> > def establish(self: object, db_name: str, charset: str = >>> "utf8mb4"): >>> > self.conn = sqlalchemy.create_engine( >>> > "mysql+pymysql://" + self.user + ":" + self.password + >>> "@" + self.host + ":" + self.port + "/" + db_name + >>> > "?charset=" + charset) >>> > print("Connection with database : %s has been established >>> as %s at %s." % (db_name, self.user, self.host)) >>> > print("Charset : %s" % charset) >>> > >>> > def retrieve(self, table): >>> > df = pd.read_sql_table(table, self.conn) >>> > return df >>> > >>> > def save(self: object, df: "Pandas.DataFrame", table: str, >>> if_exists: str = "replace", chunksize: int = 10000): >>> > df.to_sql(name=table, con=self.conn, if_exists=if_exists, >>> chunksize=chunksize) >>> > >>> > Some elements that might help: >>> > [![Database is of charset utf8mb4][2]][2] >>> > [![Table columns don't seem to have the correct collation even though >>> I specified it when creating the db][3]][3] >>> > >>> > >>> > [1]: https://stackoverflow.com/questions/2108824/mysql- >>> incorrect-string-value-error-when-save-unicode-string-in-django >>> > [2]: https://i.stack.imgur.com/9iAPF.png >>> > [3]: https://i.stack.imgur.com/qBq6U.png >>> > >>> > -- >>> > SQLAlchemy - >>> > The Python SQL Toolkit and Object Relational Mapper >>> > >>> > http://www.sqlalchemy.org/ >>> > >>> > To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> > --- >>> > You received this message because you are subscribed to the Google >>> Groups "sqlalchemy" group. >>> > To unsubscribe from this group and stop receiving emails from it, send >>> an email to sqlalchemy+...@googlegroups.com. >>> > To post to this group, send email to sqlal...@googlegroups.com. >>> > Visit this group at https://groups.google.com/group/sqlalchemy. >>> > For more options, visit https://groups.google.com/d/optout. >>> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
