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.
