Inserting data using python loop to sql table

Question

I wrote a web scraping code to get some data from a website that works so far. I connected Python to SQL because I need the data to be MySQL saved. The connection between Python and SQL is working fine, I created the SQL table with Python which I commented on the code below. My problem is while insertion the data into the SQL table, the insert script should be working fine but I'm getting a syntax error that I couldn't correct. Does anybody have an idea?

Here is the code:

import mysql.connector
db_connection = mysql.connector.connect(
host="141.45.91.40",
user="s0566293",
passwd="*******",
database="s0566293_projekt"
)
db_cursor = db_connection.cursor()
#Here creating database table as film'
#db_cursor.execute("CREATE TABLE Filme (filmName VARCHAR(255))") #already exists.

#Get database table'
db_cursor.execute("SHOW TABLES")
for table in db_cursor:
    print(table)
from urllib.request import urlopen as uReq
from bs4 import BeautifulSoup as soup
import requests
import re

my_url = 'https://www.berlin.de/kino/_bin/azfilm.php'

# opening up connection , grabbing the page
uClient = uReq(my_url)
page_html = uClient.read()
uClient.close()

#html parsing
page_soup = soup(page_html,"html.parser")

# grab any Zeichen 
az = page_soup.findAll("div",{"class":"block"})
fil = az[0]
sei = fil.findAll("li")


for s in sei:
    i=s.text
    if (i == '#'):
        continue
    else:
        seite = "https://www.berlin.de/kino/_bin/azfilm.php/de/{}".format(i)
        r = requests.get(seite)
        seiten_soup = soup(r.content, 'html.parser')
        #print page url
        #print("-------------------------------------------------------\n" + seite)
    #Filme jeder Seite

    # opening up connection , grabbing the page
        uClient = uReq(seite)
        page_html = uClient.read()
        uClient.close()

    #html parsing
        page_soup = soup(page_html,"html.parser")

    # grab any film
        az = page_soup.findAll("div",{"class":"inner"})
        fil = az[0]
        filme = fil.findAll("li")
        for film in filme:

                db_cursor.execute("INSERT INTO Filme(filmName) VALUES("+film.contents[0].contents[0]+")")
                db_cursor.execute("SHOW Filme")
                #print(film.contents[0].contents[0])

You haven't given the error and you're not using correct practice to insert into your DB to avoid SQL Injection. — roganjosh
– roganjosh, Commented Nov 14, 2019 at 18:18
WARNING: When using mysql you should be using parameterized queries and bind_param to add any data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug.it can be very harmful if someone seeks to exploit your mistake. — Shaheer
– Shaheer, Commented Nov 14, 2019 at 18:28
db_cursor.execute("INSERT INTO Filme(filmName) VALUES(%s)", (film.contents[0].contents[0])) i have tried this, it still doesnt work though. — hmu535
– hmu535, Commented Nov 14, 2019 at 19:07
You're missing the comma after contents[0]. That's needed to make the second argument a tuple. — Barmar
– Barmar, Commented Nov 14, 2019 at 21:23
@Barmar i edited it and im facing another error which is : Python 'navigablestring' cannot be converted to a MySQL type. Any solution? — hmu535
– hmu535, Commented Nov 16, 2019 at 11:05

Barmar · Accepted Answer · 2019-11-14 18:19:21Z

4

You're not putting quotes around the name when you concatenate the variable.

But you shouldn't concatenate variables into SQL, you should use placeholders.

db_cursor.execute("INSERT INTO Filme(filmName) VALUES(%s)", (film.contents[0].contents[0],))

answered Nov 14, 2019 at 18:19

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

3 Comments

tadman Over a year ago

Python makes it so easy.

hmu535 Over a year ago

Its my first time writing such a script, i tried the line you wrote and the syntax problem still there. I guess he didnt like the "%s", it sounds a bit complicated for me though

Barmar Over a year ago

I'm not sure why, it looks just like the example in the documentation

Collectives™ on Stack Overflow

Inserting data using python loop to sql table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related