Can't insert values into a database

Question

I'm trying to get some data of a website and store it in a database but it's not working and I don't know what's the error. Can you help me?

Error:

Error syntax error at or near "INTEGER"
LINE 1: CREATE TABLE test(id SERIAL INTEGER PRIMARY KEY, event_date ...

Code (the website chosen was just a test):

import urllib2
from bs4 import BeautifulSoup
import psycopg2
import sys
page = urllib2.urlopen("https://en.wikipedia.org/wiki/Wushu_at_the_2014_Asian_Games_%E2%80%93_Men%27s_sanda_60_kg")
soup = BeautifulSoup(page, "lxml")
table = soup.find("table", class_="wikitable")
A = [] 
B = []
C = []
for row in table.findAll("tr"):
 cells = row.findAll("th")
 if len(cells) != 0:
 A.append(cells[0].find(text=True))
 B.append(cells[1].find(text=True))
 C.append(cells[2].find(text=True))
con = None
try:
 con = psycopg2.connect("host= 'localhost' dbname='testdb' user='postgres' password='root'")
 cur = con.cursor()
 cur.execute("CREATE TABLE test(id SERIAL INTEGER PRIMARY KEY, event_date TEXT, event_time VARCHAR)")
 cur.execute("INSERT INTO test VALUES(%s)" % A, B, C)
 con.commit()
except psycopg2.DatabaseError, e:
 if con:
 con.rollback()
 print 'Error %s' % e
 sys.exit(1)
finally:
 if con:
 con.close()

• user330315: remove the integer after serial: postgresql.org/docs/current/static/…

Answer 1

There's no SERIAL INTEGER:

CREATE TABLE test(id SERIAL PRIMARY KEY, event_date TEXT, event_time VARCHAR)

I don't know what's the error

What do you mean? I tried just the try block, and it gave a clear error message on this problem. Maybe the preceding code has other errors that kill the program.

Your INSERT also seems to have a problem. Why not CREATE the table first and call INSERT in the loop on table.findAll ?

• Bruno Viana: You were right about that but, I still get an error on the interpreter says it's here: cur.execute("INSERT INTO test VALUES(%s)" % A, B, C)
• Vesa Karjalainen: Yes, you cannot give a list as argument and that it would expand to insert multiple rows. That means you're trying to insert a single row with field types of array. Either loop on A and run INSERT in that loop, or move INSERT inside the previous loop. Also, a %s is needed for each field to be INSERTed
• Bruno Viana: I put the code like this: pastebin.com/uB8B7gfb but now i have this error: not enough arguments for format string (it's on the same line)
• Vesa Karjalainen: Your con.close is before the loop where you INSERT? The idea was that when INSERT is inside loop, you don't need lists A, B and C. Just use normal variables for current row only. Forget those Append() s And the error is because A, B, C should be (A, B, C), just remember they can't be lists! Did you read any documentation of psycopg?
• Bruno Viana: I saw a tutorial. I need to put lists because it's a table that i want to get don't i? So, i have to put the con.close on the end and delete the lists and appends and then what?