How do you insert data from text file into SQL Server table using Python?

Question

import pyodbc
import csv
conn = pyodbc.connect('Driver=ODBC Driver 17 for SQL Server;'
                      'Server=SIS10647\MSSQLSERVER14;'
                      'Database=LeelaVenkatesh;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

cursor.execute('''

               CREATE TABLE employee
               (
               empid int,
               Name nvarchar(50)
               )

               ''')

with open('C:\PYTHON\Textfiles/1.txt') as f:
    reader = csv.reader(f)
    for row in reader:
        cursor.execute("""INSERT INTO employee(empid,name)
                          VALUES(%s, %s)
                       """, row)
conn.commit()
cursor.close()
print("Done")

I am getting the following error

cursor.execute("""INSERT INTO employee(empid,name) pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

Text file data looks like

empid|Name
1|'Ram'

illusion · Accepted Answer · 2020-12-01 06:01:33Z

1

cursor.execute("""INSERT INTO employee(empid,name)
                  VALUES(?, ?)
                  """, row)

and if this doesn't work, assuming row is an array, try this:

cursor.execute("""INSERT INTO employee(empid,name)
                  VALUES(?, ?)
                  """, row[0], row[1])

edited Dec 1, 2020 at 6:01

answered Dec 1, 2020 at 5:56

illusion

1,30111 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Chanukya Over a year ago

IndexError: list index out of range this is the error iam getting

illusion Over a year ago

so what isthe structure of row? like is it an array, if so, is the length of the array fixed for all the rows?

Chanukya Over a year ago

rows are sperated by delimter |

illusion Over a year ago

could you give an example? Value of row

Chanukya Over a year ago

Added in question please look into it

|

The Thonnu · Accepted Answer · 2022-10-14 18:18:09Z

0

empid|Name
1|'Ram'
try this
empid,Name
1,Ram
with 
cursor.execute("""INSERT INTO employee(empid,name)
                  VALUES(?, ?)
                  """, row[0], row[1])

edited Oct 14, 2022 at 18:18

The Thonnu

3,6242 gold badges12 silver badges32 bronze badges

answered Oct 11, 2022 at 6:24

asarak

1

1 Comment

asarak Over a year ago

Also the file must have only values without headers, seperated by comma

Collectives™ on Stack Overflow

How do you insert data from text file into SQL Server table using Python?

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related