Re: [Tutor] MySQLdb error - PLEASE SAVE ME!
On Sat, 17 Sep 2005, Ed Hotchkiss wrote: > I think that I am having some kind of error with my csv going into the > fields and being broken apart correctly. Ed, I'd suggest using the CSV module to parse out CSV files, rather than splitting it yourself. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
[Tutor] MySQLdb error - PLEASE SAVE ME
> I dont see why your new code shouldn't work, it makes sense to me ... Danny nailed this one. He warned that your data could be short commas. You have lines with fewer than two commas. The INSERT is failing with: not enough arguments Simple fix is to skip insert if len(links) != 3. Note that we can't test without your data. Generally, it is better to keep the responsibility for testing in your hands and simply provide the error info and the background context. (Didn't mean to panic about the password, BUT I know folks who've learned the hard way that Google sees a lot more than you might expect, and pranksters really will drop your tables.) -- Lloyd Kvam Venix Corp ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] MySQLdb error - PLEASE SAVE ME!
I got the mysql db just for this very purpose, that's it :P
Ok heres the error which I am getting now, I dont see why your new code shouldn't work, it makes sense to me ...
>>> Traceback (most recent call last): File "G:\Python\myCode\Links Database\addfromtext.py", line 30, in ? cursor.execute (""" File "C:\Python24\Lib\site-packages\MySQLdb\cursors.py", line 129, in execute
self.errorhandler(self, ProgrammingError, m.args[0]) File "C:\Python24\Lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler raise errorclass, errorvalueProgrammingError: not enough arguments for format string
>>>
heres the new code ...
# Script to add links from a comma deliminated file to a MySQL database# 9/16/05
import MySQLdb
conn=MySQLdb.connect( host="www.freesql.org", user="edhotchkiss", port=3306, passwd="test1", db="links")
cursor = conn.cursor()stmt = "DROP TABLE IF EXISTS links"cursor.execute(stmt)stmt = """CREATE TABLE links ( ID INT NOT NULL auto_increment, Name TEXT, URL LONGTEXT,
Category LONGTEXT, primary key (ID))"""cursor.execute(stmt)
inp = open ("sites.txt","r")for line in inp.readlines(): #links = map(str, line.split(",")) # values are already strings links = line.split(",",2) # limit to two splits
i.e. only use first 2 commas cursor.execute (""" INSERT INTO links (Name, URL, category) VALUES (%s, %s, %s)""", links )
cursor.close()conn.close()
On 9/17/05, Python <[EMAIL PROTECTED]> wrote:
You should avoid sending the connection info to the list. Google willbe making this widely available. Pranksters *will* delete your tables.
Change your password!Including the error info would help, but chances the following changeswill fix things:stmt = """CREATE TABLE links ( ID INT NOT NULL auto_increment, ^^
Name TEXT, URL LONGTEXT, Category LONGTEXT, primary key (ID))"""for line in inp.readlines(): #links = map(str, line.split(",")) # values are already strings
links = line.split(",",2) # limit to two splits i.e. only use first 2 commas arr.append(links) # arr is not used ??? cursor.execute (""" INSERT INTO links (Name, URL, category)
VALUES (%s, %s, %s)""", links )You are not supplying an ID value. I assume that you want MySQL to fillit in for you. So you need to make ID an auto_increment field.
The cursor.execute is now getting *two* arguments, the sql and thevalues for the insert. Do not interpolate your values into the SQLstring. Leave that to the MySQLdb module. The %s in the VALUES servesas a placeholder for the module and should not be used by you with the
Python string format (%) operator. This should work so long as the nameand URL never contain commas.--Lloyd KvamVenix Corp-- edward hotchkiss
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
[Tutor] MySQLdb error - PLEASE SAVE ME!
You should avoid sending the connection info to the list. Google will
be making this widely available. Pranksters *will* delete your tables.
Change your password!
Including the error info would help, but chances the following changes
will fix things:
stmt = """CREATE TABLE links (
ID INT NOT NULL auto_increment,
^^
Name TEXT,
URL LONGTEXT,
Category LONGTEXT,
primary key (ID)
)"""
for line in inp.readlines():
#links = map(str, line.split(",")) # values are already strings
links = line.split(",",2) # limit to two splits i.e. only use first 2
commas
arr.append(links) # arr is not used ???
cursor.execute ("""
INSERT INTO links (Name, URL, category)
VALUES (%s, %s, %s)""", links
)
You are not supplying an ID value. I assume that you want MySQL to fill
it in for you. So you need to make ID an auto_increment field.
The cursor.execute is now getting *two* arguments, the sql and the
values for the insert. Do not interpolate your values into the SQL
string. Leave that to the MySQLdb module. The %s in the VALUES serves
as a placeholder for the module and should not be used by you with the
Python string format (%) operator. This should work so long as the name
and URL never contain commas.
--
Lloyd Kvam
Venix Corp
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] MySQLdb error - PLEASE SAVE ME!
On Sat, 17 Sep 2005, Ed Hotchkiss wrote:
> Ok. I am trying to read a csv file with three strings separated by
> commas. I am trying to insert them into a MySQL DB online. MySQLdb is
> installed, no problems.
>
> I think that I am having some kind of error with my csv going into the
> fields and being broken apart correctly.
Hi Ed,
Can you show us what error you're seeing? Maybe it is related to the csv
splitting... Then again, maybe it isn't. *grin*
If you have an error message, then it'll be good to show that to us on the
list, because then we can try to help you interpret the error message.
That way, next time you seem a similar error, you might have a better idea
what the system's trying to tell you.
Let's look at some of the code.
##
arr=[]
inp = open ("sites1.txt","r")
#read line into array
for line in inp.readlines():
links = map(str, line.split(","))
arr.append(links)
cursor.execute ("""
INSERT INTO links (Name, URL, category)
VALUES (%s, %s, %s)""" % tuple(links[0:3])
)
##
Ok, I see it. I'll focus on the cursor execute() bug, but there's
possibly another one: if the sites1.txt file has lines that don't contain
two commas, then it won't be valid to try to process the data on an
incomplete line. You may want to do a check to warn and skip such broken
lines.
Try to avoid doing direct string formatting when you're working with
databases: let the database do it for you. cursor.execute() can take in
an additional tuple argument of values, which it'll use to fill in the
'%s' placeholders in the SQL.
So rather than:
cursor.execute ("""
INSERT INTO links (Name, URL, category)
VALUES (%s, %s, %s)""" % tuple(links[0:3])
)
Do this instead:
cursor.execute ("""
INSERT INTO links (Name, URL, category)
VALUES (%s, %s, %s)""", tuple(links[0:3])
)
The reason this is important is because SQL uses a different set of rules
for string quotation than Python, and it's really easy to mess it up.
This is one of those things that everyone gets mixed up about. See the
thread on 'escape-quoting strings' here:
http://mail.python.org/pipermail/tutor/2004-November/032943.html
and read that thread for more details on using cursor.execute() with that
second argument.
Best of wishes to you!
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
[Tutor] MySQLdb error - PLEASE SAVE ME!
Ok. I am trying to read a csv file with three strings separated by commas.I am trying to insert them into a MySQL DB online.MySQLdb is installed, no problems.I think that I am having some kind of error with my csv going into the
fields and being broken apart correctly. Can someone please help? Iattached the code below, it does work with that SQL server also if youwant to try and run it. Thanks in advance ..-# Script to add links from a comma deliminated file to a MySQL database
# 9/16/05import MySQLdbconn=MySQLdb.connect( host="www.freesql.org",user="edhotchkiss",port=3306,passwd="test1", db="links")
cursor = conn.cursor()stmt = "DROP TABLE IF EXISTS links"cursor.execute(stmt)stmt = """CREATE TABLE links ( ID INT NOT NULL, Name TEXT, URL LONGTEXT, Category LONGTEXT,
primary key (ID))"""cursor.execute(stmt)arr=[]inp = open ("sites1.txt","r")#read line into arrayfor line in inp.readlines(): links = map(str, line.split
(",")) arr.append(links) cursor.execute ("""INSERT INTO links (Name, URL, category) VALUES (%s, %s, %s)""" % tuple(links[0:3]) )cursor.close()conn.close
()--edward hotchkiss
# Script to add links from a comma deliminated file to a MySQL database
# 9/16/05
import MySQLdb
conn=MySQLdb.connect( host="www.freesql.org",
user="edhotchkiss",
port=3306,
passwd="test1",
db="links")
cursor = conn.cursor()
stmt = "DROP TABLE IF EXISTS links"
cursor.execute(stmt)
stmt = """CREATE TABLE links (
ID INT NOT NULL,
Name TEXT,
URL LONGTEXT,
Category LONGTEXT,
primary key (ID)
)"""
cursor.execute(stmt)
arr=[]
inp = open ("sites1.txt","r")
#read line into array
for line in inp.readlines():
links = map(str, line.split(","))
arr.append(links)
cursor.execute ("""
INSERT INTO links (Name, URL, category)
VALUES (%s, %s, %s)""" % tuple(links[0:3])
)
cursor.close()
conn.close()
O'reilly Python Archive,http://python.oreilly.com/archive.html,python
Python Tutorials,http://www.awaretek.com/tutorials.html,python
MySQL Python,http://sourceforge.net/projects/mysql-python,python
Python Vaults of Parnassus,http://www.vex.net/parnassus/,python
PyCrypto,http://www.amk.ca/python/code/crypto,Python
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
