Import a textfile to MS SQL with python

[joel.sjoo]

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()

 

[Steve Holden]

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')

Thanks for letting us know the administrator password for your database.
You might want to consider changing it (unless you modified this line
before posting).

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

This is much better expressed as something like the following (untested):

stmt = "insert into python (id, namn, efternamn) values (?, ?, ?)"
for line in inpfile:
mycursor.execute(stmt, tuple(line.split()))

Note that the "(?, ?, ?)" list of parameter markers assumes that pymssql
uses the "qmark" paramstyle, you'll have to check the documentation if
you get SQL syntax errors or similar - I couldn't easily find a
reference on the web.

The point of passing the tuple of data values as a second argument to
the .execute() method is to have the DB module take care of any
necessary quoting and representation issues. Otherwise values that (for
example) include a singel quotes, such as "O'Reilly" can be problematical.

inpfile.close()
myconn.commit()
myconn.close()

regards
Steve