notebook.community

Edit and run



In [20]:

    
from modelFactoryPy import main
from sqlalchemy.dialects import registry
import pandas as pd



In [21]:

    
registry.register('aster', 'sqlalchemy_mf_aster.jdbc', 'AsterDialect_jdbc')



In [22]:

    
main.getConnection('aster')









    Out[22]:





Engine(aster://vecht499:***@queen02.ds.gen.local:2406/beehive)



In [23]:

    
schema = "try_sqlalchemy"



In [24]:

    
connection = main.engine.connect()
connection.execute("drop table if exists "+schema+".run_history")
connection.execute("drop schema if exists " + "try_sqlalchemy")









    Out[24]:





<sqlalchemy.engine.result.ResultProxy at 0x7fdcb31fd9d0>



In [25]:

    
connection.execute("create schema " + "try_sqlalchemy")
connection.execute("create dimension table "+schema+".run_history as \
                   (select * from model_factory.run_history where session_id like '%titanic%')")









    Out[25]:





<sqlalchemy.engine.result.ResultProxy at 0x7fdccd6da110>



In [26]:

    
a = connection.execute("select * from "+schema+".run_history")
b = a.fetchall()
pd.DataFrame.from_records(b, columns=a.keys())









    Out[26]:






  
    
      
      session_id
      user_id
      model_id
      start_time
      end_time
      last_exported
      data_set_last_update
      data_set_training_start
    
  
  
    
      0
      vecht499_titanic_training_20160719_672615
      vecht499
      titanic_training
      2016-07-19 11:20:58
      None
      None
      None
      None
    
    
      1
      vecht499_titanic_training_20160719_996515
      vecht499
      titanic_training
      2016-07-19 11:25:10
      None
      None
      None
      None
    
    
      2
      molan499_titanic_training_20160905_331073
      molan499
      titanic_training
      2016-09-05 13:27:16
      2016-09-05 13:27:27
      None
      None
      None
    
    
      3
      timme463_titanic_training_20160908_99602
      timme463
      titanic_training
      2016-09-08 15:35:26
      2016-09-08 15:36:59
      None
      None
      None
    
    
      4
      kirst500_titanic_training_20160908_83185
      kirst500
      titanic_training
      2016-09-08 16:35:22
      2016-09-08 16:36:20
      None
      None
      None
    
    
      5
      molan499_titanic_training_20160908_465223
      molan499
      titanic_training
      2016-09-08 17:03:59
      2016-09-08 17:04:09
      None
      None
      None
    
    
      6
      vecht499_titanic_training_20170131_330314
      vecht499
      titanic_training
      2017-01-31 17:00:13
      None
      None
      None
      None
    
    
      7
      vecht499_titanic_training_20161127_250954
      vecht499
      titanic_training
      2016-11-27 00:33:30
      2016-11-27 00:33:53
      None
      None
      None
    
    
      8
      vecht499_titanic_training_20161201_412144
      vecht499
      titanic_training
      2016-12-01 15:06:36
      2016-12-01 15:07:36
      None
      None
      None
    
    
      9
      valk012_titanic_training_20161208_741606
      valk012
      titanic_training
      2016-12-08 18:03:51
      None
      None
      None
      None
    
    
      10
      fd255008_titanic_training_20170101_171774
      fd255008
      titanic_training
      2017-01-01 21:40:04
      2017-01-01 21:40:07
      None
      None
      None
    
    
      11
      fd255008_titanic_training_20170101_558621
      fd255008
      titanic_training
      2017-01-01 22:12:27
      2017-01-01 22:12:30
      None
      None
      None
    
    
      12
      molan499_titanic_training_20160901_523017
      molan499
      titanic_training
      2016-09-01 12:39:50
      2016-09-01 12:40:01
      None
      None
      None
    
    
      13
      zom002_titanic_training_20160720_444659
      zom002
      titanic_training
      2016-07-20 14:36:37
      2016-07-20 14:37:19
      None
      None
      None
    
    
      14
      vecht499_titanic_training_20160719_823605
      vecht499
      titanic_training
      2016-07-19 11:13:47
      None
      None
      None
      None
    
    
      15
      vecht499_titanic_training_20160719_549688
      vecht499
      titanic_training
      2016-07-19 14:04:27
      2016-07-19 14:05:05
      None
      None
      None
    
    
      16
      molan499_titanic_training_20160901_764764
      molan499
      titanic_training
      2016-09-01 12:00:40
      2016-09-01 12:00:51
      None
      None
      None
    
    
      17
      molan499_titanic_training_20160901_836467
      molan499
      titanic_training
      2016-09-01 12:38:27
      2016-09-01 12:38:38
      None
      None
      None
    
    
      18
      everd499_titanic_training_20160908_854775
      everd499
      titanic_training
      2016-09-08 15:35:05
      2016-09-08 15:36:43
      None
      None
      None
    
    
      19
      molan499_titanic_training_20160908_668096
      molan499
      titanic_training
      2016-09-08 16:54:25
      2016-09-08 16:54:37
      None
      None
      None
    
    
      20
      fd255008_titanic_training_20161110_546773
      fd255008
      titanic_training
      2016-11-10 13:25:22
      2016-11-10 13:26:36
      None
      None
      None
    
    
      21
      vecht499_titanic_training_20161112_604495
      vecht499
      titanic_training
      2016-11-12 20:04:50
      None
      None
      None
      None
    
    
      22
      odbc_user_titanic_training_20161115_587878
      odbc_user
      titanic_training
      2016-11-15 09:35:28
      None
      None
      None
      None
    
    
      23
      boual504_titanic_training_20161124_269220
      boual504
      titanic_training
      2016-11-24 11:08:50
      2016-11-24 11:10:38
      None
      None
      None
    
    
      24
      vecht499_titanic_training_20161127_558264
      vecht499
      titanic_training
      2016-11-27 12:52:47
      2016-11-27 12:53:11
      None
      None
      None
    
    
      25
      fd255008_titanic_training_20161222_749664
      fd255008
      titanic_training
      2016-12-22 22:56:19
      2016-12-22 22:56:22
      None
      None
      None
    
    
      26
      fd255008_titanic_training_20160721_824027
      fd255008
      titanic_training
      2016-07-21 12:05:15
      2016-07-21 12:06:04
      None
      None
      None
    
    
      27
      thoma505_titanic_training_20160720_894796
      thoma505
      titanic_training
      2016-07-20 14:37:57
      2016-07-20 14:38:05
      None
      None
      None
    
    
      28
      molan499_titanic_training_20160907_291248
      molan499
      titanic_training
      2016-09-07 15:22:14
      2016-09-07 15:22:26
      None
      None
      None
    
    
      29
      molan499_titanic_training_20160908_311723
      molan499
      titanic_training
      2016-09-08 14:28:58
      2016-09-08 14:29:19
      None
      None
      None
    
    
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
    
    
      335
      vecht499_titanic_training_20161116_972077
      vecht499
      titanic_training
      2016-11-16 17:34:59
      2016-11-16 17:52:28
      None
      None
      None
    
    
      336
      everd499_titanic_training_20161124_547238
      everd499
      titanic_training
      2016-11-24 14:14:26
      2016-11-24 14:15:35
      None
      None
      None
    
    
      337
      zwenn500_titanic_training_20161128_131988
      zwenn500
      titanic_training
      2016-11-28 09:34:46
      2016-11-28 09:35:45
      None
      None
      None
    
    
      338
      vecht499_titanic_training_20161128_199369
      vecht499
      titanic_training
      2016-11-28 12:42:17
      None
      None
      None
      None
    
    
      339
      vecht499_titanic_training_20161130_844578
      vecht499
      titanic_training
      2016-11-30 15:28:05
      2016-11-30 15:28:43
      None
      None
      None
    
    
      340
      fd255008_titanic_training_20161229_798814
      fd255008
      titanic_training
      2016-12-29 13:27:20
      2016-12-29 13:27:23
      None
      None
      None
    
    
      341
      vecht499_titanic_training_20170102_852653
      vecht499
      titanic_training
      2017-01-02 10:56:00
      2017-01-02 10:56:04
      None
      None
      None
    
    
      342
      fd255008_titanic_training_20170116_692247
      fd255008
      titanic_training
      2017-01-16 13:19:03
      None
      None
      None
      None
    
    
      343
      zom002_titanic_training_20160720_793197
      zom002
      titanic_training
      2016-07-20 14:33:38
      2016-07-20 14:33:47
      None
      None
      None
    
    
      344
      vecht499_titanic_training_20160719_646039
      vecht499
      titanic_training
      2016-07-19 13:30:03
      None
      None
      None
      None
    
    
      345
      fd255008_titanic_training_20160721_795385
      fd255008
      titanic_training
      2016-07-21 12:14:02
      2016-07-21 12:14:51
      None
      None
      None
    
    
      346
      fd255008_titanic_training_20160809_831774
      fd255008
      titanic_training
      2016-08-09 11:14:03
      2016-08-09 11:14:58
      None
      None
      None
    
    
      347
      molan499_titanic_training_20160901_837695
      molan499
      titanic_training
      2016-09-01 12:42:05
      2016-09-01 12:42:16
      None
      None
      None
    
    
      348
      kirst500_titanic_training_20160908_405638
      kirst500
      titanic_training
      2016-09-08 15:35:03
      None
      None
      None
      None
    
    
      349
      kirst500_titanic_training_20160908_405638
      kirst500
      titanic_training
      2016-09-08 16:31:47
      None
      None
      None
      None
    
    
      350
      vecht499_titanic_training_20160719_927799
      vecht499
      titanic_training
      2016-07-19 13:54:21
      None
      None
      None
      None
    
    
      351
      vecht499_titanic_training_20160831_137634
      vecht499
      titanic_training
      2016-08-31 16:49:58
      2016-08-31 16:50:56
      None
      None
      None
    
    
      352
      vecht499_titanic_training_20160901_950839
      vecht499
      titanic_training
      2016-09-01 12:34:34
      2016-09-01 12:35:32
      None
      None
      None
    
    
      353
      vecht499_titanic_training_20160906_360363
      vecht499
      titanic_training
      2016-09-06 14:30:25
      2016-09-06 14:32:14
      None
      None
      None
    
    
      354
      molan499_titanic_training_20160908_798491
      molan499
      titanic_training
      2016-09-08 14:28:42
      2016-09-08 14:28:58
      None
      None
      None
    
    
      355
      molan499_titanic_training_20160908_246901
      molan499
      titanic_training
      2016-09-08 14:38:21
      2016-09-08 14:38:33
      None
      None
      None
    
    
      356
      boer534_titanic_training_20160908_619447
      boer534
      titanic_training
      2016-09-08 16:12:52
      None
      None
      None
      None
    
    
      357
      molan499_titanic_training_20160908_629184
      molan499
      titanic_training
      2016-09-08 17:13:05
      2016-09-08 17:13:15
      None
      None
      None
    
    
      358
      fd255008_titanic_training_20161110_938151
      fd255008
      titanic_training
      2016-11-10 13:28:47
      2016-11-10 13:30:03
      None
      None
      None
    
    
      359
      vecht499_titanic_training_20161121_699384
      vecht499
      titanic_training
      2016-11-21 16:02:24
      2016-11-21 16:03:45
      None
      None
      None
    
    
      360
      vecht499_titanic_training_20161127_525652
      vecht499
      titanic_training
      2016-11-27 12:54:11
      2016-11-27 12:54:34
      None
      None
      None
    
    
      361
      vecht499_titanic_training_20161128_145060
      vecht499
      titanic_training
      2016-11-28 15:34:28
      2016-11-28 15:34:30
      None
      None
      None
    
    
      362
      vecht499_titanic_training_20161213_494857
      vecht499
      titanic_training
      2016-12-13 16:48:44
      2016-12-13 16:49:43
      None
      None
      None
    
    
      363
      fd255008_titanic_training_20161223_402493
      fd255008
      titanic_training
      2016-12-23 21:20:58
      None
      None
      None
      None
    
    
      364
      vecht499_titanic_training_20161229_711724
      vecht499
      titanic_training
      2016-12-29 15:47:06
      2016-12-29 15:48:13
      None
      None
      None
    
  

365 rows × 8 columns



In [27]:

    
connection.execute("drop table if exists "+schema+".run_history")
connection.execute("drop schema if exists " + "try_sqlalchemy")

connection.close()

	session_id	user_id	model_id	start_time	end_time	last_exported	data_set_last_update	data_set_training_start
0	vecht499_titanic_training_20160719_672615	vecht499	titanic_training	2016-07-19 11:20:58	None	None	None	None
1	vecht499_titanic_training_20160719_996515	vecht499	titanic_training	2016-07-19 11:25:10	None	None	None	None
2	molan499_titanic_training_20160905_331073	molan499	titanic_training	2016-09-05 13:27:16	2016-09-05 13:27:27	None	None	None
3	timme463_titanic_training_20160908_99602	timme463	titanic_training	2016-09-08 15:35:26	2016-09-08 15:36:59	None	None	None
4	kirst500_titanic_training_20160908_83185	kirst500	titanic_training	2016-09-08 16:35:22	2016-09-08 16:36:20	None	None	None
5	molan499_titanic_training_20160908_465223	molan499	titanic_training	2016-09-08 17:03:59	2016-09-08 17:04:09	None	None	None
6	vecht499_titanic_training_20170131_330314	vecht499	titanic_training	2017-01-31 17:00:13	None	None	None	None
7	vecht499_titanic_training_20161127_250954	vecht499	titanic_training	2016-11-27 00:33:30	2016-11-27 00:33:53	None	None	None
8	vecht499_titanic_training_20161201_412144	vecht499	titanic_training	2016-12-01 15:06:36	2016-12-01 15:07:36	None	None	None
9	valk012_titanic_training_20161208_741606	valk012	titanic_training	2016-12-08 18:03:51	None	None	None	None
10	fd255008_titanic_training_20170101_171774	fd255008	titanic_training	2017-01-01 21:40:04	2017-01-01 21:40:07	None	None	None
11	fd255008_titanic_training_20170101_558621	fd255008	titanic_training	2017-01-01 22:12:27	2017-01-01 22:12:30	None	None	None
12	molan499_titanic_training_20160901_523017	molan499	titanic_training	2016-09-01 12:39:50	2016-09-01 12:40:01	None	None	None
13	zom002_titanic_training_20160720_444659	zom002	titanic_training	2016-07-20 14:36:37	2016-07-20 14:37:19	None	None	None
14	vecht499_titanic_training_20160719_823605	vecht499	titanic_training	2016-07-19 11:13:47	None	None	None	None
15	vecht499_titanic_training_20160719_549688	vecht499	titanic_training	2016-07-19 14:04:27	2016-07-19 14:05:05	None	None	None
16	molan499_titanic_training_20160901_764764	molan499	titanic_training	2016-09-01 12:00:40	2016-09-01 12:00:51	None	None	None
17	molan499_titanic_training_20160901_836467	molan499	titanic_training	2016-09-01 12:38:27	2016-09-01 12:38:38	None	None	None
18	everd499_titanic_training_20160908_854775	everd499	titanic_training	2016-09-08 15:35:05	2016-09-08 15:36:43	None	None	None
19	molan499_titanic_training_20160908_668096	molan499	titanic_training	2016-09-08 16:54:25	2016-09-08 16:54:37	None	None	None
20	fd255008_titanic_training_20161110_546773	fd255008	titanic_training	2016-11-10 13:25:22	2016-11-10 13:26:36	None	None	None
21	vecht499_titanic_training_20161112_604495	vecht499	titanic_training	2016-11-12 20:04:50	None	None	None	None
22	odbc_user_titanic_training_20161115_587878	odbc_user	titanic_training	2016-11-15 09:35:28	None	None	None	None
23	boual504_titanic_training_20161124_269220	boual504	titanic_training	2016-11-24 11:08:50	2016-11-24 11:10:38	None	None	None
24	vecht499_titanic_training_20161127_558264	vecht499	titanic_training	2016-11-27 12:52:47	2016-11-27 12:53:11	None	None	None
25	fd255008_titanic_training_20161222_749664	fd255008	titanic_training	2016-12-22 22:56:19	2016-12-22 22:56:22	None	None	None
26	fd255008_titanic_training_20160721_824027	fd255008	titanic_training	2016-07-21 12:05:15	2016-07-21 12:06:04	None	None	None
27	thoma505_titanic_training_20160720_894796	thoma505	titanic_training	2016-07-20 14:37:57	2016-07-20 14:38:05	None	None	None
28	molan499_titanic_training_20160907_291248	molan499	titanic_training	2016-09-07 15:22:14	2016-09-07 15:22:26	None	None	None
29	molan499_titanic_training_20160908_311723	molan499	titanic_training	2016-09-08 14:28:58	2016-09-08 14:29:19	None	None	None
...	...	...	...	...	...	...	...	...
335	vecht499_titanic_training_20161116_972077	vecht499	titanic_training	2016-11-16 17:34:59	2016-11-16 17:52:28	None	None	None
336	everd499_titanic_training_20161124_547238	everd499	titanic_training	2016-11-24 14:14:26	2016-11-24 14:15:35	None	None	None
337	zwenn500_titanic_training_20161128_131988	zwenn500	titanic_training	2016-11-28 09:34:46	2016-11-28 09:35:45	None	None	None
338	vecht499_titanic_training_20161128_199369	vecht499	titanic_training	2016-11-28 12:42:17	None	None	None	None
339	vecht499_titanic_training_20161130_844578	vecht499	titanic_training	2016-11-30 15:28:05	2016-11-30 15:28:43	None	None	None
340	fd255008_titanic_training_20161229_798814	fd255008	titanic_training	2016-12-29 13:27:20	2016-12-29 13:27:23	None	None	None
341	vecht499_titanic_training_20170102_852653	vecht499	titanic_training	2017-01-02 10:56:00	2017-01-02 10:56:04	None	None	None
342	fd255008_titanic_training_20170116_692247	fd255008	titanic_training	2017-01-16 13:19:03	None	None	None	None
343	zom002_titanic_training_20160720_793197	zom002	titanic_training	2016-07-20 14:33:38	2016-07-20 14:33:47	None	None	None
344	vecht499_titanic_training_20160719_646039	vecht499	titanic_training	2016-07-19 13:30:03	None	None	None	None
345	fd255008_titanic_training_20160721_795385	fd255008	titanic_training	2016-07-21 12:14:02	2016-07-21 12:14:51	None	None	None
346	fd255008_titanic_training_20160809_831774	fd255008	titanic_training	2016-08-09 11:14:03	2016-08-09 11:14:58	None	None	None
347	molan499_titanic_training_20160901_837695	molan499	titanic_training	2016-09-01 12:42:05	2016-09-01 12:42:16	None	None	None
348	kirst500_titanic_training_20160908_405638	kirst500	titanic_training	2016-09-08 15:35:03	None	None	None	None
349	kirst500_titanic_training_20160908_405638	kirst500	titanic_training	2016-09-08 16:31:47	None	None	None	None
350	vecht499_titanic_training_20160719_927799	vecht499	titanic_training	2016-07-19 13:54:21	None	None	None	None
351	vecht499_titanic_training_20160831_137634	vecht499	titanic_training	2016-08-31 16:49:58	2016-08-31 16:50:56	None	None	None
352	vecht499_titanic_training_20160901_950839	vecht499	titanic_training	2016-09-01 12:34:34	2016-09-01 12:35:32	None	None	None
353	vecht499_titanic_training_20160906_360363	vecht499	titanic_training	2016-09-06 14:30:25	2016-09-06 14:32:14	None	None	None
354	molan499_titanic_training_20160908_798491	molan499	titanic_training	2016-09-08 14:28:42	2016-09-08 14:28:58	None	None	None
355	molan499_titanic_training_20160908_246901	molan499	titanic_training	2016-09-08 14:38:21	2016-09-08 14:38:33	None	None	None
356	boer534_titanic_training_20160908_619447	boer534	titanic_training	2016-09-08 16:12:52	None	None	None	None
357	molan499_titanic_training_20160908_629184	molan499	titanic_training	2016-09-08 17:13:05	2016-09-08 17:13:15	None	None	None
358	fd255008_titanic_training_20161110_938151	fd255008	titanic_training	2016-11-10 13:28:47	2016-11-10 13:30:03	None	None	None
359	vecht499_titanic_training_20161121_699384	vecht499	titanic_training	2016-11-21 16:02:24	2016-11-21 16:03:45	None	None	None
360	vecht499_titanic_training_20161127_525652	vecht499	titanic_training	2016-11-27 12:54:11	2016-11-27 12:54:34	None	None	None
361	vecht499_titanic_training_20161128_145060	vecht499	titanic_training	2016-11-28 15:34:28	2016-11-28 15:34:30	None	None	None
362	vecht499_titanic_training_20161213_494857	vecht499	titanic_training	2016-12-13 16:48:44	2016-12-13 16:49:43	None	None	None
363	fd255008_titanic_training_20161223_402493	fd255008	titanic_training	2016-12-23 21:20:58	None	None	None	None
364	vecht499_titanic_training_20161229_711724	vecht499	titanic_training	2016-12-29 15:47:06	2016-12-29 15:48:13	None	None	None