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.youbi.idri...@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+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.