Pandas could not convert string to float when reading from SQL database

Question

I have a SQLite database created with SQLAlchemy which has the format below

Code     Names              Amt1    Amt2   Amt3    Amt4
502      Real Management     11.4   1.3    -        -
5TG      80Commercial        85.8   152    4.845    4.12%
AZG      Equipment Love      11.6   117.1  -        -

But when I tried to read this into a pandas dataframe by using

pandas.read_sql('sqlite_table', con=engine)

It returns me with an error ValueError: could not convert string to float: '-'

I get that pandas can't register - in the Dataframe, but how can I work around this? Is it possible to read it - as 0 or something??

Yep. I tried read_sql_table but it gives me the same error message — jake wong
– jake wong, Commented May 8, 2016 at 9:09
Can you try updating your Amt3 then? remove - and try again — Deusdeorum
– Deusdeorum, Commented May 8, 2016 at 9:15
What is your table definition, i.e. what is the type of the Atm columns? — agold
– agold, Commented May 8, 2016 at 9:34

Deusdeorum · Accepted Answer · 2016-05-08 10:44:53Z

1

Update all rows in Amt3 with - (if you have set up your login auths and defined cursor) will be something like this:

cur.execute("UPDATE sqlite_table SET Amt3 = 0 WHERE Amt3 = '-'")

This seems to work fine for me, even with -, what is the type of your Atm3?

import pandas as pd
import sqlite3

con = sqlite3.connect(r"/Users/hugohonorem/Desktop/mytable.db") #create database

conn = sqlite3.connect('mytable.db') #connect to database
c = con.cursor() #set 

c.execute('''CREATE TABLE mytable
             (Code text, Names text, Atm1 integer, Atm2 integer, Atm3 integer, Atm4 integer)''')

c.execute("INSERT INTO mytable VALUES ('502', 'Real Management', '11.4', '1.3', '-' , '-')")
conn.commit()

So we have replicated your table, we can now remove your -

c.execute("UPDATE mytable SET Atm3 = NULL WHERE Atm3 = '-'") #set it to null

df = pd.read_sql("SELECT * from mytable", con)
print df

This will give us the output:

  Code            Names  Atm1 Atm2  Atm3 Atm4
   502  Real Management  11.4  1.3  None    -

However, as you can see I can retrieve the table with Atm4 being -

edited May 8, 2016 at 10:44

answered May 8, 2016 at 9:21

Deusdeorum

1,4262 gold badges14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

agold Over a year ago

Is it possible to do Atm3=- in sqlite? And wouldn't it be more correct to use the null value insead.

agold Over a year ago

But you should use quotes Atm='-' to check for strings (text in sqlite).

jake wong Over a year ago

Thanks Hugo! The above statement works for me. Although, I think its better to put the table and the columns in "quotes" in case you have spaces in the column names.

clow539 · Accepted Answer · 2019-04-09 15:25:51Z

0

I know this question is old but I ran into the same problem recently and this was the first result on google. What solved my problem was just to change pandas.read_sql('sqlite_table', con=engine) to the full query pandas.read_sql('SELECT * FROM sqlite_table', con=engine) as it seems to bypass the conversion attempt and it wasn't needed to edit the table.

edited Apr 9, 2019 at 15:25

answered Apr 9, 2019 at 15:17

clow539

234 bronze badges

Collectives™ on Stack Overflow

Pandas could not convert string to float when reading from SQL database

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related