Insert data to SQL Server with Python (3.7.4)

Question

I am trying to insert data to my table in Microsoft SQL Server using a Python script.

Initially I am trying to upload an excel file, but got error messages and have tried to lesser the scale of the task. At this point I am trying to push some data trough to an already existing table in my database. In this example my servername is SERVERNAME, database is DATABASENAME and my table is TABLE. The script is:

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=SERVERNAME;'
                      'Database=DATABASENAME;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('INSERT INTO DATABASENAME.dbo.TABLE(Name, Age, City)
VALUES ('Alan', '30', 'London');

for row in cursor:
    print(row)

I get this error message:

Traceback (most recent call last):
  File "<pyshell#38>", line 1, in <module>
    exec(open("C:\\pandasql.py").read())
  File "<string>", line 9
    cursor.execute('INSERT INTO DATABASENAME.dbo.TABLE (Name, Age, City)
                                                                       ^
SyntaxError: EOL while scanning string literal

I want the one row with data to be seen in my database. What am I doing wrong?

Turtlefight · Accepted Answer · 2019-08-16 12:54:40Z

If you want to use single quotes within a single-quote string you need to escape them by adding a \ before them - or using a double-quote string.

Example:

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=SERVERNAME;'
                      'Database=DATABASENAME;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute("INSERT INTO DATABASENAME.dbo.TABLE(Name, Age, City) VALUES ('Alan', '30', 'London')")

If you want your changes to be saved to the database, you also need to call commit on the connection or set autocommit to True:

# either enable autocommit
conn = pyodbc.connect('...CONNECTION STRING...', autocommit=True)

# or after inserting the row:
conn.commit()

If you want to retrieve the resulting row, you need to select it first, e.g.:

cursor.execute("SELECT * FROM DATABASENAME.dbo.TABLE")
for row in cursor:
    print(row)

or use an OUTPUT clause on your INSERT statement:

cursor.execute("""
  INSERT INTO DATABASENAME.dbo.TABLE(Name, Age, City)
  OUTPUT Inserted.Name, Inserted.Age, Inserted.City
  VALUES ('Alan', '30', 'London')
""")

for row in cursor:
    print(row)

Full example for your code snippet:

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=SERVERNAME;'
                      'Database=DATABASENAME;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute("""
  INSERT INTO DATABASENAME.dbo.TABLE(Name, Age, City)
  OUTPUT Inserted.Name, Inserted.Age, Inserted.City
  VALUES ('Alan', '30', 'London')
""")
for row in cursor:
    print(row)

conn.commit()

Nico Haase · Accepted Answer · 2019-08-16 11:39:20Z

1

The colors in the snippet should already show you the problem: you are using a single quote to begin the string and end your string somewhere in between through using another single quote.

A simple way would be to escape that qoute (through adding a \ in front); a better way that also helps to secure your code against SQL injection would be to use prepared statements

answered Aug 16, 2019 at 11:39

Nico Haase

12.1k39 gold badges48 silver badges96 bronze badges

1 Comment

gustavhf Over a year ago

Thank you! I see that mistake now and how stupid it is. Still I did not get it quite to work. Do you think you could change the code snippet, please? :)

Collectives™ on Stack Overflow

Insert data to SQL Server with Python (3.7.4)

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