Insert into sql table data with datetime and multiple rows using Python

Question

I have created sql table with following code:

CREATE Table [dataISO].[dbo].[LMP](
    [DateTime] [datetime] NOT NULL, 
    [ISO] [nvarchar](50) NOT NULL,
    [pwrLoc] [nvarchar](50) NOT NULL,
    [da] [int] NULL,
    [rt] [int] NULL
        CONSTRAINT [PK_daLMP] PRIMARY KEY CLUSTERED
        (
            [DateTime] ASC,
            [ISO] ASC, 
            [pwrLoc] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 20) ON [PRIMARY]
        ) ON [PRIMARY]

I have a dataframe with columns as:

data.ix[1:4]
             DateTime    ISO  pwrLoc            DA             RT
1 2013-09-05 02:00:00  ERCOT  LZ_AEN            24.66          25.4100
2 2013-09-05 03:00:00  ERCOT  LZ_AEN            23.17          24.3750
3 2013-09-05 04:00:00  ERCOT  LZ_AEN            22.20          23.9225
4 2013-09-05 05:00:00  ERCOT  LZ_AEN            22.45          23.9625

I am trying the following to insert data into table LMP:

    values = [tuple(row) for row in data.values]
    dateInfoQry = 'insert into [dataISO].[dbo].[LMP] (DateTime, ISO, pwrLoc, da, rt) values {}'.format(values)
    conn = pyodbc.connect(r'Driver={SQL SERVER}; SERVER=2UA7491NP7\SQLEXPRESS')
    conn.execute(dateInfoQry)

I get the following error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with '(Timestamp('2013-09-05 01:00:00'), 'ERCOT', 'LZ_AEN', 28.31, 34.5325), (Timestamp('2013-09-05 02:00:00'), 'ERCOT', 'LZ_AEN', 24.' is too long. Maximum length is 128. (103) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '(Timestamp('2013-09-05 01:00:00'), 'ERCOT', 'LZ_AEN', 28.31, 34.5325), (Timestamp('2013-09-05 02:00:00'), 'ERCOT', 'LZ_AEN', 24.'. (102)")

Answer 1

The error says the string is too long, probably the insert statement is not well formatted here. Always try to use the single quotes for the string in the SQL Server.

The string has the "[]", so it needs formatting as well.

 insert into [dataISO].[dbo].[LMP] (DateTime, ISO, pwrLoc, da, rt) values [('2013-09-05 02:00:00', 'ERCOT LZ_AEN', '24.66', '25.4100'),('2013-09-05 02:00:00', 'ERCOT LZ_AEN', '24.66', '25.4100')]

So if we modify the code to something like this

values = [tuple(row) for row in data]
 dateInfoQry = 'insert into [dataISO].[dbo].[LMP] (DateTime, ISO, pwrLoc, da, rt) values {}'.format(','.join(str(s) for s in values))

the insert statement looks pretty ok.

insert into [dataISO].[dbo].[LMP] (DateTime, ISO, pwrLoc, da, rt) values ('2013-09-05 02:00:00', 'ERCOT', 'LZ_AEN', 4.66, 5.41),('2013-09-05 02:00:00', 'ERCOT', ' LZ_AEN', 24.66, 25.41)

It runs fine.

Give it a shot

Answer 2

When I print your query dateInfoQry, I get this result:

insert into [dataISO].[dbo].[LMP] (DateTime, ISO, pwrLoc, da, rt) values [('2013-09-05 02:00:00', 'ERCOT', 'LZ_AEN', 24.66, 25.41), ('2013-09-05 03:00:00', 'ERCOT', 'LZ_AEN', 23.17, 24.375), ('2013-09-05 04:00:00', 'ERCOT', 'LZ_AEN', 22.2, 23.9225), ('2013-09-05 05:00:00', 'ERCOT', 'LZ_AEN', 22.45, 23.9625)]

You can see that there are still some square brackets because you include the list values as a whole (see .format(values)). However, if I build a string from that using .format(','.join(str(v) for v in values)), I get the following result which should work:

insert into [dataISO].[dbo].[LMP] (DateTime, ISO, pwrLoc, da, rt) values ('2013-09-05 02:00:00', 'ERCOT', 'LZ_AEN', 24.66, 25.41),('2013-09-05 03:00:00', 'ERCOT', 'LZ_AEN', 23.17, 24.375),('2013-09-05 04:00:00', 'ERCOT', 'LZ_AEN', 22.2, 23.9225),('2013-09-05 05:00:00', 'ERCOT', 'LZ_AEN', 22.45, 23.9625)