i have pandas dataframe ordered.
a0 b0 c0 d0 370025442 370020440 370020436 \ 1 31/08/2014 first yorkshire 53 05:10 0 0.8333 1.2167 2 31/08/2014 first yorkshire 53 07:10 0 0.85 1.15 3 31/08/2014 first yorkshire 53 07:40 0 0.5167 0.7833 4 31/08/2014 first yorkshire 53 08:10 0 0.7 1 5 31/08/2014 first yorkshire 53 08:40 nan nan nan 6 31/08/2014 first yorkshire 53 09:00 0 0.5 0.7667 7 31/08/2014 first yorkshire 53 09:20 0 0.5833 1 8 31/08/2014 first yorkshire 53 09:40 0 0.4 0.7 9 31/08/2014 first yorkshire 53 10:20 0 0.5333 1.0333 10 31/08/2014 first yorkshire 53 10:40 0 0.4833 1 11 31/08/2014 first yorkshire 53 11:00 0 0.3667 0.7 12 31/08/2014 first yorkshire 53 11:20 0 0.5333 1.15 13 31/08/2014 first yorkshire 53 11:40 0 0.3333 0.7667 14 31/08/2014 first yorkshire 53 12:00 0 1.0167 1.5 15 31/08/2014 first yorkshire 53 12:40 0 0.75 1.0333 .. ... ... .. ... ... ... ... 737 25/10/2014 first yorkshire 53 21:40 0 1.0167 1.3 738 25/10/2014 first yorkshire 53 22:40 0 0.5667 1 however, when convert sql, ordering altered (row 13 onwards) , becomes:
a0 b0 c0 d0 370025442 370020440 370020436 \ 0 31/08/2014 first yorkshire 53 05:10 0 0.8333 1.2167 1 31/08/2014 first yorkshire 53 07:10 0 0.85 1.15 2 31/08/2014 first yorkshire 53 07:40 0 0.5167 0.7833 3 31/08/2014 first yorkshire 53 08:10 0 0.7 1 4 31/08/2014 first yorkshire 53 08:40 none none none 5 31/08/2014 first yorkshire 53 09:00 0 0.5 0.7667 6 31/08/2014 first yorkshire 53 09:20 0 0.5833 1 7 31/08/2014 first yorkshire 53 09:40 0 0.4 0.7 8 31/08/2014 first yorkshire 53 10:20 0 0.5333 1.0333 9 31/08/2014 first yorkshire 53 10:40 0 0.4833 1 10 31/08/2014 first yorkshire 53 11:00 0 0.3667 0.7 11 31/08/2014 first yorkshire 53 11:20 0 0.5333 1.15 12 31/08/2014 first yorkshire 53 14:00 0 0.4833 1.0167 13 31/08/2014 first yorkshire 53 16:20 0 0.6833 1.15 14 31/08/2014 first yorkshire 53 23:10 none none none .. ... ... .. ... ... ... ... 736 25/10/2014 first yorkshire 53 21:40 0 1.0167 1.3 737 25/10/2014 first yorkshire 53 22:40 0 0.5667 1 the data correct, it's ordering of rows has been altered (this confirmed looking @ sql table within sql server management studio). checked input table both before , after operation , remains unaltered, ordering issue must when converted sql.
the code used create sql table is:
engine = sqlalchemy.create_engine("mssql+pyodbc://*server*?driver=sql+server+native+client+10.0?trusted_connection=yes") conn = engine.connect() art_array.to_sql(theartsql, engine, if_exists="replace", index=false) (where server specified)
what might causing , how might resolve it? appreciated...
edit: should mention versions using are:
python version: 2.7.8
pandas version: 0.15.1
sqlalchemy version: 1.0.12
these required maintained compatible other software.
that normal. sql tables not maintain row order. need "order by" correct order. include row id (or index) prior moving data sql. so, can "order by" in sql.
try this:
df 0 1.00 1 2.00 2 0.67 3 1.34 print df.reset_index().to_sql(xxxx) index 0 0 1.00 1 1 2.00 2 2 0.67 3 3 1.34 then in sql, can "order by" index.. "order by" syntax can vary depending on sql database.
Comments
Post a Comment