How to mix python mysql.connector and newspaper libraries?

Question

I'm currently making a program which searches through the BBC News website and adds each headline to a MySQL database using the mysql.connector and newspaper libraries. I've written the following code but nothing happens when I run it, I was wondering what I'm doing wrong? Apologies if it's just a simple mistake :)

import mysql.connector
import newspaper

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="headlines"
)

search = newspaper.build('https://www.bbc.co.uk/news')
for article in search.articles:
    mycursor = mydb.cursor()

    sql = "insert into headlines (headline) values (%s)", (article.title)

    mycursor.execute(sql)

    mydb.commit()

    print(mycursor.rowcount, "headlines inserted.")

score 0 · Accepted Answer · answered Feb 11 '19 at 19:07

Currently, you are neither interpolating your article.title to SQL string with modulo operator nor enclosing string literals with quotes. Also, do note: the string modulo formatting is an old, non-recommended version for string formatting, de-emphasized but not officially deprecated.

sql = "insert into headlines (headline) values (%s)", (article.title)

In fact, sql returns as a tuple which is not the expected first argument in cur.execute()

print(type(sql))
# <class 'tuple'>

Possibly, you meant to parameterize your query as MySQL's placeholder, %s, is similar to string modulo placeholder. Doing so properly, you avoid any string interpolation or quote enclosures:

# INITIALIZE CURSOR OUTSIDE LOOP
mycursor = mydb.cursor()

# PREPARED STATEMENT (SCALAR STRING)
sql = "insert into headlines (headline) values (%s)"

search = newspaper.build('https://www.bbc.co.uk/news')

for article in search.articles:
    # BIND PARAM AS ONE-ITEM TUPLE
    mycursor.execute(sql, (article.title,))
    mydb.commit()

    # print(mycursor.rowcount, "headlines inserted.")   -- rowcount IS N/A FOR ACTION QUERIES

How to mix python mysql.connector and newspaper libraries?

1 Answers1