generating SQL - problem
Steve Holden
sholden at holdenweb.com
Wed Aug 8 18:22:06 EDT 2001
"Lee" <lee.reilly at ntlworld.com> wrote in message
news:3B71AA2D.E9D219B7 at ntlworld.com...
> I wonder if someone could offer me a little advice. I have a large
> (155KB) text file holding MP3 data in the format:
>
> Filename with path ; Title ; Artistd
> d:\\SRS\\Breed.mp3 ; Breed ; Snake River Conspiracy
>
> I want to parse this file and create a single SQL insert statement, but
> I am having a few problems. I have the following script, which works
> fine (may not be pretty, but it works ;-)
>
> #
>
,---------------------------------------------------------------------------
--+
>
> # | createMP3SQL.py
> # |
> # | import string
> # |
> # | fileobject=open('newdata.txt', 'r') # open the playlist
> # | line = fileobject.readline() # read the first line
> # |
> # | FullSQL = '' # the final query string
> # | end = 'false' # test condition - true when end of file is reached
> # | while (end!='true'):
> # | try:
> # | line = fileobject.readline()
> # | trackdata=string.split(line, ";")
> # | file=string.rstrip(trackdata[0])
> # | title=string.lstrip(string.rstrip(trackdata[1]))
> # | artist=string.lstrip(string.rstrip(trackdata[2]))
> # | SQL = 'insert into MP3s values (\'' + file + '\', \'' + title +
> '\', \''
> # | + artist + '\')'
> # | FullSQL = FullSQL + SQL + ";"
> # | except Exception:
> # | end='true'
> #
>
`---------------------------------------------------------------------------
--+
>
> When I run this script is takes ~5 secs to process, and when I type in
> 'FullSQL' to display the text (the SQL string) in IDLE it halts
> completely. It works fine with smaller test data. I am using Python 2.0
> on Win98. Can anyone suggest what the problem is and how it can be
> remedied?
>
The problem is with IDLE's display of very large strings. Pythonwin has
similar problems.
The *real* question would be why you want to create all these statements
when a single one would do.
What you could do (unless you are wedded to huge SQL batches :-) is as
follows (untested hack warning):
import string
fileobject=open('newdata.txt', 'r') # open the playlist
data = []
while 1:
line = fileobject.readline() # read a line
if not line:
break
trackdata=string.split(line, ";")
data.append(
(string.rstrip(trackdata[0]),
string.lstrip(string.rstrip(trackdata[1])),
string.lstrip(string.rstrip(trackdata[2]))
)
)
data = tuple(data)
dbconn = (expression to open the database of your choice)
cursor = conn.cursor()
cursor.executemany(
"INSERT INTO MP3s (fname1, fname2, fname3) VALUES (?, ?, ?)",
data
)
dbconn.commit()
Of course you may want to run the SQL through some databse you only have
text access to, in which case this won't work. But for experimentation you
could get a copy of mxODBC and use that.
I've re-cast your loop into the recommended Pythonic form, which you will
note makes things a little simpler. As to why you would want to see such a
huge statement, I have no idea!
regards
Steve
--
http://www.holdenweb.com/
More information about the Python-list
mailing list