1

I have the problem with pandas method: read_sql I select four columns from database and about 100 rows.

import pymysql
sql = "SELECT `opens`,`high`,`low`,`close` FROM `dbname`"
result = ""
try:
    with c as cursor:
      cursor.execute(sql)
      result = cursor.fetchall()
    conn.commit()
finally:
    conn.close()
print(result)

Result of this above example returns values in proper format, whit good precision for double and data stored in a database.

opens       high        low         close
0.00016445  0.00016445  0.00016445  0.00016445

But with:

import pandas as pd
df = pd.read_sql(sql,conn,coerce_float=False)
print(df)

The result is:

opens     high      low       close
0.000164  0.000164  0.000164  0.000164

I need to use data like are in a database and that cause a lot of issues during resampling data. Can anyone help me with that?

I was trying to change casting, and added that options 'coerce_float=False' but that seems to be worthless

EDIT 9.07.2018 - 1:

SQL TABLE SHAME:

 CREATE TABLE `dbname` (
  `ids` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` int(10) unsigned NOT NULL,
  `high` double NOT NULL,
  `low` double NOT NULL,
  `opens` double NOT NULL,
  `close` double NOT NULL,
  `volume` double NOT NULL,
  `weightedAverage` double NOT NULL,
  PRIMARY KEY (`ids`)
) ENGINE=InnoDB AUTO_INCREMENT=16113 DEFAULT CHARSET=latin1

EDIT 10.07.2018 - 2: Resampling example:

df.set_index('timestamp', inplace=True)

#converting timestamp to DatatimeIndex
df.index = pd.to_datetime(df.index, unit='s')
conversion = {'opens': 'first', 'high': 'max', 'low': 'min', 'close': 'last'}

#df = df.resample('15Min', how = conversion, base=0)
 df = df.resample(self.period, closed='right').agg(conversion)
print(df)

EDIT 11.07.2018 - EDIT 1 I have partially solution for that problem: I added before any action: 

pd.set_option('display.float_format', lambda x: ('%.8f' % x).rstrip('.0'))

Alright, everything works fine with that settings. I solved that problem. Maybe someone will have the same problem and quick find my solution.

Rick James
  • 135,179
  • 13
  • 127
  • 222
mrberner
  • 96
  • 2
  • 8
  • OK, I added the table schema. – mrberner Jul 09 '18 at 19:03
  • Printing result from `pd.read_sql(...)` has that problem, I cannot make `.iloc` on dataframe after read query from the database to change precision. That issue happend as result of query – mrberner Jul 09 '18 at 19:18
  • I did it. And result is the same. Still it is missing two digits. `df = pd.read_sql(sql,conn,coerce_float=False) print(df)` or `df = pd.read_sql(sql,conn,coerce_float=False) print(df.iloc[0,0])` give the same result – mrberner Jul 09 '18 at 19:31
  • Result : -0.8242454332434314 – mrberner Jul 09 '18 at 19:44
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/174677/discussion-between-mrberner-and-parfait). – mrberner Jul 09 '18 at 19:47
  • So after long education I found it (I suppose) when I use conversion, there is `max` function which in result round everything, but what is a purpose of round data which must be precise? – mrberner Jul 10 '18 at 20:38
  • `MAX()` does not "round". Maybe the rounding is during displaying?? This rounds: '%.8f' – Rick James Sep 30 '18 at 04:19
  • '%.8f - this is formatting of float number to be prettier with 8 decimal places. – mrberner Dec 30 '18 at 01:07

0 Answers0