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.
