CODEWITHSUNDEEP
sqlsql-servert-sqlpython-3.5
Zanam
Zanam

Reputation: 4807

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

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

Upvotes: 3

Views: 1044

Answers (3)

Lemon
Lemon

Reputation: 141

Try to add:

SET QUOTED_IDENTIFIER OFF

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

Upvotes: 1

Vikas Sharma
Vikas Sharma

Reputation: 501

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

Upvotes: 4

user10343866
user10343866

Reputation:

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)

Upvotes: 4

Related Questions