How to insert values in a column name with space in sql using python

user_8586856 0
Viewed 712

SQL database is like

ID Fname Lname Full Name
1 jon bela jon bela
2 cena dabi cena dabi

I have created a Stored Procedure named Info I want to insert more data into this table throught python. I have a data frame named "df" read in csv file. I wanted to insert csv file value into SQL database.

python code

    import pyodbc
    import pandas as pd
    
    df = pd.read_csv("file path")
    server = "myservername"
    database = "mydatabasename"
    con = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL server};\
           SERVER =' +server+';\
           DATABASE =' +databse+';\
           Trusted_Connection=yes;')
   cursor = con.cursor()
   for index, row in df.iterrows():
       cursor.execute("INSERT INTO info (ID, Fname, Lname, Full Name) 
       values(?,?,?,?)", row.ID, row.Fname, row.Lname, row.Full Name)


   con.commit()

after running this code I got an error "invalid syntax" Full Name. How to access column name with space

stored-procedures sql python
3 Answers

Not sure if the answer is still relevant, however, as the previous answer was not correct the solution below might help to other people facing this issue.

Insert data from csv file into sql with assumption that column names are not included in the file:

import csv
import pyodbc

server = "myservername"
database = "mydatabasename"

con = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL server};\
           SERVER =' +server+';\
           DATABASE =' +databse+';\
           Trusted_Connection=yes;')

with open ('file path', 'r') as f:
    reader = csv.reader(f)
    data = next(reader)
    query = 'INSERT INTO database values ({0})'
    query = query.format(','.join('?' * len(data)))
    cursor = conn.cursor()
    for data in reader:
        cursor.execute(query, data)
    cursor.commit()
user_167458440 
enter code here
import pyodbc
import pandas as pd

df = pd.read_csv("file path")
server = "myservername"
database = "mydatabasename"
con = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL server};\
           SERVER =' +server+';\
           DATABASE =' +databse+';\
           Trusted_Connection=yes;')
cursor = con.cursor()
for index, row in df.iterrows():
    cursor.execute("INSERT INTO info ([ID], [Fname], [Lname], [Full Name]) 
               values(?,?,?,?)", row.ID, row.Fname, row.Lname, row['Full 
Name'])
con.commit()
user_197000630

row.Full Name should be row['Full Name'] also in your sql query use [Full Name] or"Full Name" (in python use triple """ INSERT .... """ when usign "" for column name )

user_52604000
Related