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

Vikas Sharma · Accepted Answer · 2018-09-13 04:50:28Z

4

+50

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

edited Sep 13, 2018 at 4:50

answered Sep 12, 2018 at 12:04

Vikas Sharma

5015 silver badges8 bronze badges

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

Comments

user10343866 · Accepted Answer · 2018-09-11 18:45:09Z

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)

Lemon · Accepted Answer · 2018-09-17 12:26:56Z

1

Try to add:

SET QUOTED_IDENTIFIER OFF

before your insert into it should help, as [] are similar to "" in SQL.

answered Sep 17, 2018 at 12:26

Lemon

1412 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related