Import a textfile to MS SQL with python
in [Python]
|
Prev: py2exe and libxml
Next: Unicode / cx_Oracle problem
From: joel.sjoo@gmail.com on 6 Sep 2006 03:51 I'm a dba for SQL server and I Will import a textfile to SQL. For example I use a file with 3 columns. ID, Name and Surname and the columns are tab separated. I don't know much about programming. Anyway, I use this code below. It works, but it will not split the columns. I have tried to change the argumnts in str(alllines[]) Some of the columns can include many characters and some not. For exampel names can be Bo or Lars-Ture. I be glad if some can help me with this. Regar Joel import pymssql import string,re myconn = pymssql.connect(host='lisa',user='sa',password='AGpu83!#',database='junk') mycursor = myconn.cursor() inpfile=open('c:\\temp\\test.txt','r') for alllines in inpfile.read().split('\n'): stmt="insert into python (id, namn, efternamn) values ('%s', '%s', '%s')" %(str(alllines[0]),str(alllines[2:10]),str(alllines[3:10])) mycursor.execute(stmt) print stmt inpfile.close() myconn.commit() myconn.close()
From: Tim Golden on 6 Sep 2006 04:13 [joel.sjoo(a)gmail.com] | I'm a dba for SQL server and I Will import a textfile to SQL. Not a Python answer, but unless you're in it for the learning experience, I *seriously* suggest you look at the built-in BULK INSERT command to see if it meets your needs. Random URL: http://www.sqlteam.com/item.asp?ItemID=3207 If it doesn't, then by all means post back and I'm sure we can talk you through the Python side of things.. TJG ________________________________________________________________________ This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
From: joel.sjoo@gmail.com on 6 Sep 2006 04:53 i know the bulk instert functions i ms sql but i will use this script for oracle in a linux enviroment to so i think python is a good choice. regard joel
From: Tim Golden on 6 Sep 2006 05:53 [joel.sjoo(a)gmail.com] | I'm a dba for SQL server and I Will import a textfile to SQL. For | example I use a file with 3 columns. ID, Name and Surname and the | columns are tab separated. I don't know much about programming. | Anyway, I use this code below. It works, but it will not split the | columns. Split the problem into two parts: 1) Determine the correct row/column values from your tab-separated file 2) Write the values into your database table The first part is probably best handled by the built-in csv module. While you can roll your own there are quite a few gotchas you have to dodge - embedded delimiters and so on. Something like this: <code> import csv # by default reader uses "," as delimiter; specify tab instead reader = csv.reader (open ("test.tsv"), delimiter="\t") data = [] for line in reader: data.append (line) # or data = list (reader) print data # # Something like: # [[1, "Tim", "Golden"], [2, "Fred", "Smith"], ...] # </code> OK, now you've got a list of lists, each entry being one row in your original file, each item one column. To get it into your database, you'll need something like the following -- ignoring the possibility of executemany. <code> # uses data from above import <database module> # pymssql, oracle, sqlite, etc. db = <database module>.connect (... whatever you need ....) q = db.cursor () for row in data: q.execute ( "INSERT INTO python (id, namn, efternamn) VALUES (?, ?, ?)", row ) db.commit () # if needed etc. db.close () </code> This works because the DB-API says that an .execute takes as its first parameter the SQL command plus any parameters as "?" (or something else depending on the paramstyle, but this is probably the most common). Then as the second parameter you pass a list/tuple containing as many items as the number of "?" in the command. You don't need to worry about quoting for strings etc; the db interface module should take care of that. Behind the scenes, this code will be doing something like this for you: INSERT INTO python (id, namn, efternamn) VALUES (1, 'Tim', 'Golden') INSERT INTO python (id, namn, efternamn) VALUES (2, 'Fred', 'Smith') and so on, for all the rows in your original data. Some db interface modules implement .executemany, which means that you specify the statement once and pass the whole list at one go. Whether it's more efficient than looping yourself depends on what's happening behind the scenes. It's certainly a touch tidier.. Hope all that is intelligble and helpful TJG ________________________________________________________________________ This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
From: joel.sjoo@gmail.com on 6 Sep 2006 06:28
Ok thanks Tim. I'm possible to read the file now as you described but when I pass it to the DB I got an error says: [['1', 'Joel', 'Sjoo'], ['2', 'Sture', 'Andersson'], ['3', 'Arne', 'Svensson']] Traceback (most recent call last): File "txttosql6.py", line 23, in ? row File "C:\Python24\Lib\site-packages\pymssql.py", line 120, in execute self.executemany(operation, (params,)) File "C:\Python24\Lib\site-packages\pymssql.py", line 146, in executemany raise DatabaseError, "internal error: %s (%s)" % (self.__source.errmsg(), se lf.__source.stdmsg()) pymssql.DatabaseError: internal error: None (None) I dont know if it is the pymssql module that not work with this code. I a code that you described. import csv import pymssql reader = csv.reader (open ("c:\\temp\\test.txt"), delimiter="\t") data = [] for line in reader: data.append (line) myconn = pymssql.connect(host='lisa',user='sa',password='',database='junk') mycursor = myconn.cursor() for row in data: mycursor.execute( "INSERT INTO python (id, namn, efternamn) VALUES (?, ?, ?)", row ) db.commit () # if needed etc. db.close () |