txt insert to sql server using python

Question

I have a txt file. Each line has 4 numbers separated with a space. (line example, 1243814474 832 23.5380533333333 37.88067). I want to insert every number of every line in a 4-column table in sql server respectively (1243814474 in column 1, 832 in column 2 etc.). The code I give to you inserts only the 2nd, 4th, 6th and 8th digit of the first number of the line (example, from 1243814474 it takes 2 to 1st column, 3 to 2nd column, 1 to 3rd column and 4 to 4th column).

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=my_server;DATABASE=test;UID=myusername;PWD=mypassword')
cursor = cnxn.cursor()
with open("information.txt") as infile:
    for line in infile:
        cursor.execute("insert into temp1(tmstmp, shipno, lon, lat) values (" + line[1] + ", " + line[3] +", " + line[5] + ", " + line[7] +")")
        cnxn.commit()

You need to read about, understand, and start using parameterized queries before bobby tables comes to visit. bobby-tables.com — Sean Lange
– Sean Lange, Commented Jun 14, 2016 at 13:28

A-y · Accepted Answer · 2016-06-14 13:51:55Z

When you write :

for line in infile:

line is a string, not a list. line[0] will return the first character of that string

you can use the split(" ") function to turn a string in a list of strings by splitting it on spaces

Also, use parametrized queries to ensure sql injection is not an issue

Finally, breaking long strings by surrounding them with parenthesis helps a lot with readability. no one wants to scroll horizontally to be able to see all your code.

with open("information.txt") as infile:
    for line in infile:
        data = line.split(" ")
        query = ("insert into temp1(tmstmp, shipno, lon, lat) "
                 "values (?, ?, ?, ?)")
        cursor.execute(query, data[0], data[1], data[2], data[3])
        cnxn.commit()

Or simply :

with open("information.txt") as infile:
    for line in infile:
        data = line.split(" ")
        query = ("insert into temp1(tmstmp, shipno, lon, lat) "
                 "values (?, ?, ?, ?)")
        cursor.execute(query, *data)
        cnxn.commit()

giannis makridakis · Accepted Answer · 2016-06-18 10:29:27Z

0

thank you! i removed the " " from line.plit(" ") and it worked properly!

answered Jun 18, 2016 at 10:29

giannis makridakis

271 silver badge2 bronze badges

1 Comment

A-y Over a year ago

Split() will by default split on spaces, so the " " isn't necessary. However, what you are trying to do is clearer with the delimiter specified.

Collectives™ on Stack Overflow

txt insert to sql server using python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related