In [2]:
from sqlalchemy import create_engine
import pandas as pd
In [9]:
engine = create_engine('postgresql://celia@localhost:5432/mytestdb')
engine
Out[9]:
In [20]:
df_customer.to_json('/tmp/test.json')
In [6]:
json_df = pd.read_json('/home/celia/Downloads/MOCK_DATA.json')
json_df
Out[6]:
In [10]:
json_df.to_sql('Customer', engine, index=None)
In [15]:
df_customer = pd.read_sql_query('select email, bday, first_name from "Customer"',con=engine)
df_customer
Out[15]:
In [16]:
df_customer.info()
In [18]:
df_customer.describe()
Out[18]:
Link a Pandas NB para ver join, merge, append, etc
In [19]:
json_df.columns
Out[19]:
In [20]:
new_df = pd.DataFrame([[pd.datetime(1990, 3, 19),'cintas.celia@gmail.com', 'celia', 'Female', 10000, 'cintas']], columns=json_df.columns)
new_df
Out[20]:
In [21]:
new_df.to_sql('Customer', engine, if_exists='append', index=None)
In [32]:
df_customer = pd.read_sql_query('select * from "Customer" WHERE id = 10000;', con=engine)
df_customer
Out[32]:
In [33]:
new_table = pd.DataFrame([], columns=['WineCode', 'Type', 'Vintage'])
new_table
Out[33]:
In [ ]:
new_table.to_sql('Wine', engine, index=None)
In [36]:
from sqlalchemy import MetaData, types
from sqlalchemy import Table, Column
In [37]:
metadata = MetaData()
In [38]:
time = Table('Time', metadata,
Column('TimeCode', types.Integer, primary_key=True),
Column('Date', types.DateTime, nullable=False),
)
In [39]:
metadata.create_all(engine)
In [40]:
data = [[1, 'White', 2000],
[2, 'red', 2015],
[3, 'rose', 2014]]
In [42]:
new_df = pd.DataFrame(data, columns=new_table.columns)
In [43]:
new_df.to_sql('Wine', engine, if_exists='append', index=None)
In [44]:
df_wine = pd.read_sql_query('select * from "Wine"',con=engine)
df_wine
Out[44]:
In [45]:
data = [[1, 'White', pd.datetime(2000, 10, 10)],
[2, 'red', pd.datetime(2010, 9, 9)],
[3, 'rose', pd.datetime(2011, 9, 9)]]
In [46]:
new_df = pd.DataFrame(data, columns=df_wine.columns)
new_df['Vintage']
Out[46]:
In [56]:
new_df.to_json('/tmp/lero.json', date_unit='ns')
In [57]:
json_demo = pd.read_json('/tmp/lero.json')
json_demo
Out[57]:
In [58]:
new_df
Out[58]:
In [59]:
json_demo['Vintage'] = pd.to_datetime(json_demo['Vintage'], unit='ns')
In [60]:
json_demo
Out[60]:
In [61]:
new_df
Out[61]:
In [62]:
json_demo.columns.values
Out[62]:
In [63]:
json_demo.values
Out[63]:
In [64]:
pd.merge(json_demo, new_df, on=list(json_demo.columns.values), how='outer')
Out[64]:
In [82]:
pd.Series?
In [65]:
score = pd.Series([10, 9, 8], name='score')
score
Out[65]:
In [66]:
out = pd.concat([json_demo, score], axis=1)
new_row = pd.DataFrame([[4, 'espumeante',pd.datetime(2000,2,2)]], columns=new_df.columns)
In [67]:
append_df = new_df.append(new_row)
In [68]:
append_df.to_sql('Wine', engine, if_exists='append', index=None)