using pymysql to get data from mysql but return nan

Question

I try to get some data from mysql with python but return nan. There is my code:

import csv
import pandas as pd
from sqlalchemy import create_engine, types
from pandas.io import sql
import pymysql
import numpy as np

db = pymysql.connect(host='192.0.0.0',port=0000,user='123',passwd='123321',db='matomo',charset='utf8')
cursor = db.cursor()

try:
    SQL_Query = pd.read_sql_query(
        '''SELECT name FROM matomo_site''',db
    )
    df0=pd.DataFrame(SQL_Query, columns=['a'])

except:
    print("Error")
    db.rollback()
 
db.close()

print(df0)

and the result:

    a
0 NaN
1 NaN
2 NaN

And the mysql data on matomo_site:

idsite  name
1       Example
2       abc
3       def

I should output the data like:

    a
0 Example
1 abc
2 def

Is this because my data is in encrypted dataspace?

jezrael · Accepted Answer · 2021-10-20 05:09:00Z

1

There is problem you set column a, which not exist in DataFrame, so is returned column a filled by missing values.

You need change:

df0=pd.DataFrame(SQL_Query, columns=['a'])

to:

df0 = pd.read_sql_query(
    '''SELECT name FROM matomo_site''',db
).rename(columns={'name':'a'}).reset_index(drop=True)

because read_sql_query return DataFrame, so only added rename with Series.reset_index and drop=True if need default RangeIndex.

answered Oct 20, 2021 at 5:09

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

using pymysql to get data from mysql but return nan

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related