using pymysql to get data from mysql but return nan

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?

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating