python - How to read MySQL timestamp(6) into pandas? -

-

i have mysql table timestamps have microsecond resolution:

+----------------------------+------+ | time                       | seq  |  +----------------------------+------+ | 2015-06-19 02:17:57.389509 |    0 |  | 2015-06-19 02:17:57.934171 |   10 | +----------------------------+------+

i want read pandas dataframe. using

import pandas pd con = get_connection() result = pd.read_sql("select * mytable;", con=con) print result

returns nat (not time):

    time  seq  0   nat    0   1   nat   10

how can read timestamp?

in general, convert timestamps, can use pandas.to_datetime().

>>> import pandas pd >>> pd.to_datetime('2015-06-19 02:17:57.389509') timestamp('2015-06-19 02:17:57.389509')

from docs, when reading in sql, can explicitly force columns parsed dates:

pd.read_sql_table('data', engine, parse_dates=['date'])

or more explicitly, specify format string, or dict of arguments pass pandas.to_datetime():

pd.read_sql_table('data', engine, parse_dates={'date': '%y-%m-%d'})

or

pd.read_sql_table('data', engine, parse_dates={'date': {'format': '%y-%m-%d %h:%m:%s'}})

adding quick proof of concept. note, using sqlite. assuming storing timestamps strings:

from sqlalchemy import create_engine, table, column, integer, string, metadata import pandas pd  engine = create_engine('sqlite:///:memory:', echo=true)  datapoints = [{'ts': '2015-06-19 02:17:57.389509', 'seq': 0},               {'ts':'2015-06-19 02:17:57.934171', 'seq': 10}] metadata = metadata() mydata = table('mydata', metadata,     column('ts', string),     column('seq', integer), ) metadata.create_all(engine) ins = mydata.insert() conn = engine.connect() conn.execute(ins, datapoints)  foo = pd.read_sql_table('mydata', engine, parse_dates=['ts']) print(foo)

outputs:

                           ts  seq 0  2015-06-19 02:17:57.389509    0 1  2015-06-19 02:17:57.934171   10

or, if storing them datetime objects, works same (the code differences me getting data database in datetime format):

from datetime import datetime sqlalchemy import create_engine, table, column, integer, datetime, metadata import pandas pd  engine = create_engine('sqlite:///:memory:', echo=true)  datapoints = [{'ts': datetime.strptime('2015-06-19 02:17:57.389509', '%y-%m-%d %h:%m:%s.%f'), 'seq': 0},               {'ts':datetime.strptime('2015-06-19 02:17:57.934171', '%y-%m-%d %h:%m:%s.%f'), 'seq': 10}] metadata = metadata() mydata = table('mydata', metadata,     column('ts', datetime),     column('seq', integer), ) metadata.create_all(engine) ins = mydata.insert() conn = engine.connect() conn.execute(ins, datapoints)  foo = pd.read_sql_table('mydata', engine, parse_dates=['ts']) print(foo)

outputs same:

                          ts  seq 0 2015-06-19 02:17:57.389509    0 1 2015-06-19 02:17:57.934171   10

hope helps.

edit attempt address concern of @joris, true sqlite stores datetime objects strings, built-in adapter automatically converts these datetime objects when fetched. extending second example with:

from sqlalchemy.sql import select s = select([mydata]) res = conn.execute(s) row = res.fetchone() print(type(row['ts']))

results in <class 'datetime.datetime'>


Comments

Post a Comment

Popular posts from this blog

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops', ...
Read more

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null ...
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens ...
Read more