I'm beginner of "Python with sqlAlchemy".
I try to insert my bulk CSV data's to MySQL by using the following,
My Code :
import pandas as pd
from sqlalchemy import create_engine
df = pd.read_csv('/home/shankar/LAB/Python/company_bi.csv')
# 2nd argument replaces where conditions is False
df = df.where(pd.notnull(df), None)
df.head()
conn_str = "mysql+pymysql://root:*****@localhost/globalTracker?charset=utf8&use_unicode=0"
engine = create_engine(conn_str)
conn = engine.raw_connection()
df.to_sql(name='company', con=conn,
if_exists='append',
chunksize=10000, dtype=object,
index_label='SOS_VOTERID')
conn.close()
NOTE : In Some cases, another CSV files are working, but some of them not imported properly due to Encoding issues. Even tried "dtype" as "mysql", also i get the same.
Error I Got :
AttributeError Traceback (most recent call last)
<ipython-input-7-0c08d4874fc0> in <module>()
12 if_exists='append',
13 chunksize=10000, dtype=object,
---> 14 index_label='SOS_VOTERID')
15 conn.close()
/home/shankar/.local/lib/python3.5/site-packages/pandas/core/generic.py in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
1163 sql.to_sql(self, name, con, flavor=flavor, schema=schema,
1164 if_exists=if_exists, index=index, index_label=index_label,
-> 1165 chunksize=chunksize, dtype=dtype)
1166
1167 def to_pickle(self, path):
/home/shankar/.local/lib/python3.5/site-packages/pandas/io/sql.py in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
569 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
570 index_label=index_label, schema=schema,
--> 571 chunksize=chunksize, dtype=dtype)
572
573
/home/shankar/.local/lib/python3.5/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
1651 """
1652 if dtype is not None:
-> 1653 for col, my_type in dtype.items():
1654 if not isinstance(my_type, str):
1655 raise ValueError('%s (%s) not a string' % (
AttributeError: type object 'object' has no attribute 'items'
Kindly Notify my Bug ...
Thanks in Advance.
dtype
is supposed to be a "dict of column name to SQL type", notobject
. – univerio Aug 19 at 18:40dtype=object
, which is simply due to an incorrect argument. If your question is really about "encoding issues" then you should elaborate on what those issues are. As it stands, the code is fine for inserting rows from a CSV. – univerio Aug 20 at 20:09