CODEWITHSUNDEEP
pythonsqlsql-server
Empyz
Empyz

Reputation: 59

How to change the data type of a CSV when INSERTING the data into SQL server via Python?

I'm pulling data from a feed and saving it as a .csv file, which is then being inserted into SQL server.

However, the column "datetime" is by default, set as nvarchar(50).

How can I make it so that this datetime column converts to the datatype datetime BEFORE inserting it into the SQL table?

Also, how can I substring the datetime column into 2 separate columns such as column: date and column: time before inserting into the table?

The substring and left functions that I want to use are: SUBSTRING(datetime,12,5) as 'time' LEFT(datetime,10) as 'date'

columns = ['Datetime', 'Open', 'High', 'Low', 
        'Close', 'Adj Close', 'Volume']


df_data = df[columns]
records = df_data.values.tolist()



    
sql_insert = '''
    INSERT INTO Live_Ticker
    VALUES (?, ?, ?, ?, ?, ?, ?)
'''

Upvotes: 0

Views: 448

Answers (1)

C. White
C. White

Reputation: 800

Assuming you add the date and time columns, just cast the value to the intended type:

DECLARE @datetime datetime = ?;
sql_insert = '''
INSERT INTO Live_Ticker ("date", "time","Open","High","Low","Close","Adj Close","Volume") 
VALUES (cast(@datetime As date), cast(@datetime as time) ?, ?, ?, ?, ?, ?)

'''

Upvotes: 0

Related Questions