Python database insert

Question

I've been trying to parse a text file (opened with parameter encoding='utf8') and insert extracted values into an mdb database using pyodbc module. I have tried the code below:

for line in fp:
    tokens = line.split('\t')
    tokens[4] = tokens[4][:len(tokens[4])-1] #to avoid the \n
    tokens[1] = tokens[1][1:] #to remove the 'u' from the beginning of utf8 characters like u'\u0622'
    content = conn.execute("INSERT INTO Entries (PForm, WForm, Code, Freq, Pattern) VALUES ("+tokens[0]+","+tokens[1]+","+tokens[2]+","+tokens[3]+","+tokens[4]+")")
    conn.commit()

and received the following error: Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4. (-3010) (SQLExecDirectW)')

P.S. the first line of my file is: آ 'A Ab 1 S

And the other lines are of the same format.

Your comments will be appreciated :)

Can you put a raw line from the text file and the "Entries" schema? — Antonio Beamud
– Antonio Beamud, Commented May 9, 2013 at 9:00
The text file contains many lines, each line contains five tokens separated by a tab. The three first lines are like: آ 'A Ab 1 S\n برآ barA V1 5000 WS\n بوآ bo'A N1 1 WS\n — user3017348
– user3017348, Commented May 9, 2013 at 9:07
and the table "Entries" has exactly five columns as indicated in the 5th line of my code — user3017348
– user3017348, Commented May 9, 2013 at 9:10
can you print tokens before the execute and post here the output? — Antonio Beamud
– Antonio Beamud, Commented May 9, 2013 at 9:27

michaelmeyer · Accepted Answer · 2013-05-09 09:27:29Z

2

You don't put quotes around the strings which you want to insert. Assuming the "Freq" row is of type INTEGER:

stmt = """
INSERT INTO Entries (PForm, WForm, Code, Freq, Pattern)
    VALUES ('%s', '%s', '%s', %s, '%s')
"""

params = tuple(t for t in tokens)

conn.execute(stmt % params)

But anyway, you shouldn't be formatting an INSERT statement like this. Doesn't the library you're using provide a facility to parameterize statements ? Something like this:

conn.execute("INSERT INTO Foo VALUES (?, ?, ?)", (foo, bar, baz))

answered May 9, 2013 at 9:27

michaelmeyer

8,2657 gold badges33 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Python database insert

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related