data insert in mysql table

A forum for general discussion of the Python programming language.

data insert in mysql table

Postby b0ndar » Thu Oct 10, 2013 8:11 pm

Hey everybody

I am new to the forum and already having problems.
I am trying to get my data from an htm file into a table in mysql.

Good side is that with the code below I get no errors. Bad side is that the table is not populated.
MySQL is not complaining, Python is not complaining so please if you can take a look at what's hapening with my code...

In addition, all the variables are valid and if i try to print them again they do return with the right values

Thank you

Code: Select all
#!/usr/bin/env python
#-*- coding:utf-8 -*-

import MySQLdb
from bs4 import BeautifulSoup

db = MySQLdb.connect("localhost", "user", "pass", "work", use_unicode=True, charset = "utf8")

cursor = db.cursor()
   
soup = BeautifulSoup(open('sanitised.htm'))
elements = soup.find_all("div")

for line in elements:
   span_list = line.find_all("span")
   timestamp = span_list[0].get_text()
   id1 = span_list[1].get_text().encode('utf-8')
   id_res1 = span_list[2].get_text().encode('utf-8')
   id2 = span_list[3].get_text().encode('utf-8')
   id_res2 = span_list[4].get_text().encode('utf-8')
   mytext = span_list[5].get_text().encode('utf-8')
   cursor.execute = ("INSERT INTO chat_tbl VALUES (%s, %s, %s, %s, %s, %s);", (timestamp, id1, id_res1, id2, id_res2, mytext))
   
db.close()
b0ndar
 
Posts: 3
Joined: Thu Oct 10, 2013 8:02 pm

Re: data insert in mysql table

Postby Yoriz » Thu Oct 10, 2013 8:25 pm

Hi, welcome to the forum.
You have not called commit to apply the changes to the database.
New Users, Read This
Join the #python-forum IRC channel on irc.freenode.net!
Image
User avatar
Yoriz
 
Posts: 1020
Joined: Fri Feb 08, 2013 1:35 am
Location: UK

Re: data insert in mysql table

Postby b0ndar » Thu Oct 10, 2013 8:33 pm

Thank you for the reply.

I read that for MySQL the commit is implied so it's not necessary to be present within the code.
I tried it but still no results.
b0ndar
 
Posts: 3
Joined: Thu Oct 10, 2013 8:02 pm

Re: data insert in mysql table

Postby Crimson King » Thu Oct 10, 2013 9:55 pm

b0ndar wrote:Hey everybody

I am new to the forum and already having problems.
I am trying to get my data from an htm file into a table in mysql.

Good side is that with the code below I get no errors. Bad side is that the table is not populated.
MySQL is not complaining, Python is not complaining so please if you can take a look at what's hapening with my code...

In addition, all the variables are valid and if i try to print them again they do return with the right values

Thank you

Code: Select all
#!/usr/bin/env python
#-*- coding:utf-8 -*-

import MySQLdb
from bs4 import BeautifulSoup

db = MySQLdb.connect("localhost", "user", "pass", "work", use_unicode=True, charset = "utf8")

cursor = db.cursor()
   
soup = BeautifulSoup(open('sanitised.htm'))
elements = soup.find_all("div")

for line in elements:
   span_list = line.find_all("span")
   timestamp = span_list[0].get_text()
   id1 = span_list[1].get_text().encode('utf-8')
   id_res1 = span_list[2].get_text().encode('utf-8')
   id2 = span_list[3].get_text().encode('utf-8')
   id_res2 = span_list[4].get_text().encode('utf-8')
   mytext = span_list[5].get_text().encode('utf-8')
   cursor.execute = ("INSERT INTO chat_tbl VALUES (%s, %s, %s, %s, %s, %s);", (timestamp, id1, id_res1, id2, id_res2, mytext))
   
db.close()


Hey b0ndar, idk if this was a typo but on the line before you close the connection you wrote:

Code: Select all
cursor.execute = ("INSERT INTO chat_tbl VALUES (%s, %s, %s, %s, %s, %s);", (timestamp, id1, id_res1, id2, id_res2, mytext))


try changing that to
Code: Select all
cursor.execute("INSERT INTO chat_tbl VALUES (%s, %s, %s, %s, %s, %s);", (timestamp, id1, id_res1, id2, id_res2, mytext))


(it's the same as what you wrote without the '=')
User avatar
Crimson King
 
Posts: 131
Joined: Fri Mar 08, 2013 2:42 pm
Location: Buenos Aires, Argentina

Re: data insert in mysql table

Postby b0ndar » Fri Oct 11, 2013 7:08 am

Worked!
Million thanks
b0ndar
 
Posts: 3
Joined: Thu Oct 10, 2013 8:02 pm


Return to General Discussions

Who is online

Users browsing this forum: No registered users and 4 guests