CODEWITHSUNDEEP
pythonmysqlpymysql
Nick Duddy
Nick Duddy

Reputation: 1000

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

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()

Upvotes: 1

Views: 4962

Answers (1)

PRMoureu
PRMoureu

Reputation: 13317

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')

Upvotes: 1

Related Questions