pandas df.to_sql() remove timestamp

Question

I'm working with Pandas and SQLAlchemy to write yahoo finance prices into a SQL library. My question is, How do I remove the timestamp from the database?

Full Code

def update (symbol):
    engine = create_engine("sqlite:///test.db")  # Access the DB Engine
    try:
        if not engine.dialect.has_table(engine, symbol):  # If table does not exist. Create
            start = dt.date(2000, 1, 1)
            end = dt.datetime.today()
            get_prices = yahoo.DataReader(symbol, 'yahoo', start, end)
            get_prices.to_sql(symbol, engine, if_exists='append')

This code works, it gets the prices into a SQL database. But the date portion comes up like this 2001-01-17 00:00:00.000000.

How should I edit my code such that the timestamp doesn't show up??

By default the index (`Date`) will be of type `datetime64[ns]`. In order to convert it to `date` you can do `get_prices.index = get_prices.index.date` before you call `get_prices.to_sql`. — van, Oct 11 '15 at 10:28
@van I'm using sqlite database through sqlalchemy. I've tried to do `get_prices.index = get_prices.index.date`, and it does save it into sqlite with just the date. But it seems to be changing the column name `Date` to `index`. Is it possible to keep the column name as `Date`? — jake wong, Oct 11 '15 at 10:40
Well, in case the table is not created, could you log (and add to the question) the SQL statement with `CREATE TABLE ...`. And how do you know/see that the value is with ` 00:00:00.000000`? In any case, SQLite does not store really typed data, but *only displays* as per types defined (Read [1.2 Date and Time Datatype](https://www.sqlite.org/datatype3.html) section of documentation). But if you read data again using sqlalchemy ORM, it will convert values to the proper type for you when you query the data. — van, Oct 11 '15 at 10:55
I used SqliteBrowser to view the `.db` file that was created. then noticed that it was `2001-01-17 00:00:00.000000`. But when I read it into pandas dataframe, it seems to only draw out the date. Is it not possible for the `table` to only contain the date alone via this method? — jake wong, Oct 11 '15 at 11:48

score 0 · Answer 1 · edited May 23 '17 at 10:27

I was referring to docs as well as pandas to_sql and it gave me an idea with what Van said.

Below are the codes that I integrated, seems to give me what I want.

 def update (symbol):
    engine = create_engine("sqlite:///test.db")  # Access the DB Engine
    try:
        if not engine.dialect.has_table(engine, symbol):  # If table does not exist. Create
            start = dt.date(2000, 1, 1)
            end = dt.datetime.today()
            get_prices = yahoo.DataReader(symbol, 'yahoo', start, end)
            # The below code was provided by Van, but it changes the name 
            # of the index Column to "index" instead of "date"
            get_prices.index = get_prices.index.date
            get_prices.to_sql(symbol, engine, if_exists='append', index=True, 
                              index_label='Date')  # index_label changes the name back to "Date"

So far, this seems to be giving me what I intended. Does anyone have a better solution to it?

I think this is a good solution. By setting the index to new values (``get_prices.index = get_prices.index.date``), the index name is lost. Therefore, `to_sql` uses the 'default' name for the index ('index') and using `index_label` is the easiest way to change that (other way would be to change the index name itself: `get_prices.index.name = 'Date'`) — joris, Oct 11 '15 at 21:46

pandas df.to_sql() remove timestamp

1 Answers1