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

Answer 1

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

• Python split()
• Odbc parametrized queries