using pymysql to get data from mysql but return nan-CodePudding

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?

CodePudding user response：

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.