pyodbc bulk data import challenge using cursor.executemany( query, df.itertuples(index=False) )

Question

I am reasonably new to python but have given this a serious bash to solve this elegantly.

The challenge: I am wanting to import market data from a pandas df into a sql table. There are some 7000 different stocks and each has some 4000-10000 End of Day records so I am trying to get the pyodbc executemany working rather than updating a lot of code to use SQL Alchemy and the to_sql option.

These few lines of code have me very challenged – and required an update to SQL ODBC Driver v17 to support the index=False option.

The SQL target table:

CREATE TABLE [dbo].[EOD](
    [Exchange] [varchar](10) NOT NULL,
    [Issue] [varchar](10) NOT NULL,
    [TDate] [date] NOT NULL,
    [O] [float] NOT NULL,
    [H] [float] NOT NULL,
    [L] [float] NOT NULL,
    [C] [float] NOT NULL,
    [V] [int] NOT NULL,
    [Split] [float] NULL
) ON [PRIMARY]
GO

The data: df.head()

         date exchange issue  open  high    low  close     volume  unadj
0  2016-11-14      ASX   CGC  2.96  2.97  2.880   2.90   549167.0    0.0
1  2016-11-15      ASX   CGC  2.90  2.96  2.865   2.95   587456.0    0.0
2  2016-11-16      ASX   CGC  2.96  2.96  2.890   2.94   666295.0    0.0
3  2016-11-17      ASX   CGC  2.94  3.15  2.910   3.11  1086692.0    0.0
4  2016-11-18      ASX   CGC  3.15  3.25  3.150   3.23  2043553.0    0.0

The closest I have gotten to get this to work is as per below. However, this approach results in a:

('22007', '[22007] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

cursor = cnxn.cursor()
query = ("INSERT INTO [Securities].[dbo].[EOD] (Exchange, Issue, TDate, O, H, L, C, V, Split) "
         "VALUES (?,?,?,?,?,?,?,?,?) " )
try:
    cursor.executemany( query, df.itertuples(index=False) )

except Exception as e:
    print(e)
cursor.close()

The pandas column is already a date type – so I do not believe this is a simple date type conversion (but in desperation have tried the conversion below)

query = ("INSERT INTO [Securities].[dbo].[EOD] (Exchange, Issue, TDate, O, H, L, C, V, Split) "
         "VALUES (convert(date,?,126),?,?,?,?,?,?,?,?) " )

Any suggestions on how to make this approach work would be deeply appreciated.

/Luthor

Your DataFrame has columns ordered "date", "exchange", "issue" but your INSERT statement has columns ordered "Exchange", "Issue", "TDate". Therefore, you're probably trying to insert "issue" codes ('CGC') into the "TDate" column. — Gord Thompson, Dec 26 '18 at 13:48
OMG - After spending a lot of time learning how to do this, I got to close to the trees to not see the forest. Made column changes playing with index options leading up to the post ... Feeling stupid. ty. — Luthor, Dec 27 '18 at 00:05
Specifically with regard to using `itertuples` with `executemany`, [this GitHub issue](https://github.com/mkleehammer/pyodbc/issues/500) may be of interest. — Gord Thompson, Dec 27 '18 at 23:49
Thanks Gord - that article has been most useful. Learning more about python everyday. — Luthor, Jan 10 '19 at 10:22

score 0 · Answer 1 · answered Dec 27 '18 at 00:22

Your DataFrame has columns ordered "date", "exchange", "issue" but your INSERT statement has columns ordered "Exchange", "Issue", "TDate". Therefore, you're apparently trying to insert "issue" codes ('CGC') into the "TDate" column.

Change the column order of your INSERT statement to match the order of the columns in the DataFrame.

pyodbc bulk data import challenge using cursor.executemany( query, df.itertuples(index=False) )

1 Answers1