PyMySQL Error: 'str' object has no attribute 'to_sql'

Question

I'm new to coding and this my first project. So far I've pieced together what I have through Googling, Tutorials and Stack.

I'm trying to add data from a pandas df of scraped RSS feeds to a remote sql database. I've been using a combo of this post and this post to fix my error but the answers don't seem to work with my problem.

Does anyone have a potential solution to the error:

AttributeError Traceback (most recent call last) in () 19 cursor.execute(sql) 20 ---> 21 sql.to_sql(df, con=conn, name='rsstracker', if_exists='append', flavor='mysql') 22 23 # disconnect from server

AttributeError: 'str' object has no attribute 'to_sql'

import pandas as pd
from pandas.io import sql
import feedparser
import time

rawrss = ['http://newsrss.bbc.co.uk/rss/newsonline_uk_edition/front_page/rss.xml',
          'https://www.yahoo.com/news/rss/',
          'http://www.huffingtonpost.co.uk/feeds/index.xml',
          'http://feeds.feedburner.com/TechCrunch/',
         ]

posts = []
for url in rawrss:
    feed = feedparser.parse(url)
    for post in feed.entries:
        posts.append((post.title, post.link, post.summary))
df = pd.DataFrame(posts, columns=['title', 'link', 'summary']) # pass data to init

import pymysql

# Open database connection
db = pymysql.connect(host="host", port=##, user="username", password="password", db="sql#######" )

# prepare a cursor object using cursor() method
cursor = db.cursor()


# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS rsstracker")

# Create table as per requirement
sql = """CREATE TABLE rsstracker(
   article_title  varchar(255),
   article_url  varchar(1000),
   article_summary varchar(1000))"""

cursor.execute(sql)

sql.to_sql(df, con=conn, name='rsstracker', if_exists='append', flavor='mysql')

# disconnect from server
db.close()

Error seems clear as to_sql is a pandas dataframe method but you are running it on a sql string — Parfait
– Parfait, Commented Aug 30, 2017 at 21:14

PRMoureu · Accepted Answer · 2017-09-01 16:49:22Z

1

The line should be :

df.to_sql(con=db, name='rsstracker', if_exists='append', flavor='mysql')

But you may need to use SQLAlchemy to make this export possible (doc)

You can try to use SQLAlchemy with pymysql like in the following lines :

import pymysql
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>')
engine.execute("DROP TABLE IF EXISTS rsstracker")
engine.execute("""CREATE TABLE rsstracker(
   article_title  varchar(255),
   article_url  varchar(1000),
   article_summary varchar(1000))""")


df.to_sql(con=engine, name='rsstracker', if_exists='append', , flavor='mysql')

edited Sep 1, 2017 at 16:49

answered Aug 30, 2017 at 20:47

PRMoureu

13.4k6 gold badges46 silver badges52 bronze badges

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

12 Comments

Nick Duddy Over a year ago

I've tried this and now get the error: 'ValueError: database flavor mysql is not supported' Why do you think I may need SQLAlchemy? Is there an issue with the way I'm approaching the problem?

PRMoureu Over a year ago

it seems other flavors are not supported, so i guess you should switch to SQLAlchemy, or export the datas from dataframes with another way

Nick Duddy Over a year ago

do I swap 'pymysql' with 'SQLAlchemy'?

PRMoureu Over a year ago

@NickDuddy, could you try with the edited solution ?

Nick Duddy Over a year ago

Just changed '@<host>[:<port>]' to '@<host>:<port>'. Now getting a different error: 'UnicodeEncodeError: 'latin-1' codec can't encode character '\u2013' in position 151: ordinal not in range(256)'

|

Collectives™ on Stack Overflow

PyMySQL Error: 'str' object has no attribute 'to_sql'

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related