[FIXED] How to show float values with pandas.read_sql from a sqlite query

Issue

I need to get this result for an assignment using python/sqlite3. required result

I did my query in MYSQL, and I got the answer already to the assignment question. Since I am learning, I find it easier to do the queries using MySQL Workbench first. result in MySQLWorkbench

However, When I try to do it in Jupyter notebook with Sqlite3, it only shows the zeros on the percentage column. I am using the function pd.read_sql_query. I went to the documentation and could not find any arguments there that would do what I want, or I did not understand it. I played with the coarse_float argument, but it did not make a difference. I am learning, so sometimes, I do not understand the documentation completely.

query_results = pd.read_sql_query(query1,conn)

This is what I get in my Jupyter notebook: Output un Jupyter Notebook

I know the numbers are there because if I multiple the column ”percentage_female_only_movie” fly 100, I see them. I would like to know how to show them like in MYSQLWorkbench.

Thank you for any help. An if you know any link where I can learn about this type of issues, I would love if you can share it.

Solution

I found the solution. I needed to CAST the numerator and denominator of the column I was generating in SELECT statement of my query.

SELECT SUBSTR(TRIM(m.year),-4) AS Movie_year,
    ROUND(CAST(fcm.Female_Cast_Only_Movies*100 AS FLOAT)/ CAST(tm.movies_total AS FLOAT),2) AS Percentage_Female_only_Movie,
   tm.movies_total As Total_movies

FROM Movie AS m

output:

enter image description here

Answered By - simplemente humano