def test():
from db import DemoDB
def _test(db: DemoDB, save_inc: int, update_inc: int):
# init
db.int_val = 1
# save
db.int_val += save_inc
db.save()
assert_load_same(db, 1 + save_inc)
def _cb(val: DemoDB) -> Any:
val.int_val += update_inc
return val.int_val
db.update(_cb)
assert_load_same(db, 1 + save_inc + update_inc)
def assert_load_same(db: DemoDB, expect: int):
load_db = DemoDB().with_context(db.context).load()
assert load_db.int_val == expect
# 测试全局
_test(DemoDB(), 1, 10)
# 测试设置context
_test(DemoDB().with_context("test"), 2, 20)
def _test(db: DemoDB, save_inc: int, update_inc: int):
# init
db.int_val = 1
# save
db.int_val += save_inc
db.save()
assert_load_same(db, 1 + save_inc)
def _cb(val: DemoDB) -> Any:
val.int_val += update_inc
return val.int_val
db.update(_cb)
assert_load_same(db, 1 + save_inc + update_inc)
def test_filepath_db():
from db import DemoDB
# 测试指定路径
filepath_db = DemoDB().with_context("test_filepath").with_db_filepath("card_secrets/_local_test.txt")
filepath_db.load()
print(filepath_db.int_val)
filepath_db.int_val = 666
filepath_db.save()
print(filepath_db.prepare_env_and_get_db_filepath())
def test_table_keys_per_column(self):
short_db = DemoDB(keys_per_column=1)
self.assertEqual("""+----------------------------------------------------------------------------------------+
| Track |
+--------------+---------------+-----------------------+---------------------------------+
| Column | Type | Foreign Keys | Reference Keys |
+--------------+---------------+-----------------------+---------------------------------+
| TrackId | INTEGER | | InvoiceLine.TrackId, (+ 1 more) |
| Name | NVARCHAR(200) | | |
| AlbumId | INTEGER | Album.AlbumId | |
| MediaTypeId | INTEGER | MediaType.MediaTypeId | |
| GenreId | INTEGER | Genre.GenreId | |
| Composer | NVARCHAR(220) | | |
| Milliseconds | INTEGER | | |
| Bytes | INTEGER | | |
| UnitPrice | NUMERIC(10,2) | | |
+--------------+---------------+-----------------------+---------------------------------+""".strip(),
'{0}'.format(short_db.tables.Track.__repr__()).strip())
# -*- coding: utf-8 -*- """ Created on Tue May 14 18:29:23 2019 @author: Aluno 10 """ #pip install -U scikit-learn #pip install yellowbrick import pandas as pd from db import DemoDB database = DemoDB() print(database.tables) #Pegando a tabela de album e artista album = database.tables.Album.all() print(album.head()) #PEgando a tabela de artista artist = database.tables.Artist.all() print(artist.head()) #Juntando as tabelas album_artista = pd.merge(artist, album) print(album_artista.head()) #declarando as ligações album_artista = pd.merge(artist, album, on="ArtistId")
def setUp(self):
self.db = DemoDB()
class PandaSQLTest(unittest.TestCase):
def setUp(self):
self.db = DemoDB()
def test_query_rowsum(self):
df = self.db.query("select * from Artist;")
self.assertEqual(len(df), 275)
def test_query_groupby(self):
q = "select AlbumId, sum(1) from Track group by 1"
df = self.db.query(q)
self.assertEqual(len(df), 347)
def test_query_from_file_rowsum(self):
with open("db/tests/testscript.sql", "w") as f:
f.write("select * from Artist;")
df = self.db.query_from_file("db/tests/testscript.sql")
self.assertEqual(len(df), 275)
def test_add_profile(self):
profiles = list_profiles()
self.db.save_credentials(profile="test_profile")
self.assertEqual(len(profiles) + 1, len(list_profiles()))
remove_profile("test_profile")
def test_remove_profile(self):
profiles = list_profiles()
self.db.save_credentials(profile="test_profile")
self.assertEqual(len(profiles) + 1, len(list_profiles()))
remove_profile("test_profile")
def test_list_profiles(self):
self.db.save_credentials(profile="test_profile")
self.assertTrue(len(list_profiles()) > 0)
remove_profile("test_profile")
def test_table_head(self):
self.assertEqual(len(self.db.tables.Artist.head()), 6)
def test_table_all(self):
self.assertEqual(len(self.db.tables.Artist.all()), 275)
def test_table_select(self):
df = self.db.tables.Artist.select("ArtistId", "Name")
self.assertEqual(df.shape, (275, 2))
def test_table_sample(self):
df = self.db.tables.Artist.sample(n=10)
self.assertEqual(len(df), 10)
def test_table_uniqe(self):
df = self.db.tables.Track.unique("GenreId", "MediaTypeId")
self.assertEqual(len(df), 38)
def test_column_head(self):
col = self.db.tables.Track.TrackId.head()
self.assertEqual(len(col), 6)
def test_column_all(self):
col = self.db.tables.Track.TrackId.all()
self.assertEqual(len(col), 3503)
def test_column_sample(self):
col = self.db.tables.Track.TrackId.sample(n=10)
self.assertEqual(len(col), 10)
def test_column_unique(self):
col = self.db.tables.Customer.Country.unique()
self.assertEqual(len(col), 24)
def test_table_keys_per_column(self):
short_db = DemoDB(keys_per_column=1)
self.assertEqual(
"""+----------------------------------------------------------------------------------------+
| Track |
+--------------+---------------+-----------------------+---------------------------------+
| Column | Type | Foreign Keys | Reference Keys |
+--------------+---------------+-----------------------+---------------------------------+
| TrackId | INTEGER | | InvoiceLine.TrackId, (+ 1 more) |
| Name | NVARCHAR(200) | | |
| AlbumId | INTEGER | Album.AlbumId | |
| MediaTypeId | INTEGER | MediaType.MediaTypeId | |
| GenreId | INTEGER | Genre.GenreId | |
| Composer | NVARCHAR(220) | | |
| Milliseconds | INTEGER | | |
| Bytes | INTEGER | | |
| UnitPrice | NUMERIC(10,2) | | |
+--------------+---------------+-----------------------+---------------------------------+"""
.strip(), '{0}'.format(short_db.tables.Track.__repr__()).strip())
def tearDown(self):
pass
def test_table_count_rows(self):
count = self.db.tables.Invoice.count
self.assertEqual(count, 412)
def setUp(self):
self.db = DemoDB()
class PandaSQLTest(unittest.TestCase):
def setUp(self):
self.db = DemoDB()
def test_query_rowsum(self):
df = self.db.query("select * from Artist;")
self.assertEqual(len(df), 275)
def test_query_groupby(self):
q = "select AlbumId, sum(1) from Track group by 1"
df = self.db.query(q)
self.assertEqual(len(df), 347)
def test_query_from_file_rowsum(self):
with open("/tmp/testscript.sql", "w") as f:
f.write("select * from Artist;")
df = self.db.query_from_file("/tmp/testscript.sql")
self.assertEqual(len(df), 275)
def test_add_profile(self):
profiles = list_profiles()
self.db.save_credentials(profile="test_profile")
self.assertEqual(len(profiles)+1, len(list_profiles()))
remove_profile("test_profile")
def test_remove_profile(self):
profiles = list_profiles()
self.db.save_credentials(profile="test_profile")
self.assertEqual(len(profiles)+1, len(list_profiles()))
remove_profile("test_profile")
def test_list_profiles(self):
self.db.save_credentials(profile="test_profile")
self.assertTrue(len(list_profiles()) > 0)
remove_profile("test_profile")
def test_table_head(self):
self.assertEqual(len(self.db.tables.Artist.head()), 6)
def test_table_all(self):
self.assertEqual(len(self.db.tables.Artist.all()), 275)
def test_table_select(self):
df = self.db.tables.Artist.select("ArtistId", "Name")
self.assertEqual(df.shape, (275, 2))
def test_table_sample(self):
df = self.db.tables.Artist.sample(n=10)
self.assertEqual(len(df), 10)
def test_table_uniqe(self):
df = self.db.tables.Track.unique("GenreId", "MediaTypeId")
self.assertEqual(len(df), 38)
def test_column_head(self):
col = self.db.tables.Track.TrackId.head()
self.assertEqual(len(col), 6)
def test_column_all(self):
col = self.db.tables.Track.TrackId.all()
self.assertEqual(len(col), 3503)
def test_column_sample(self):
col = self.db.tables.Track.TrackId.sample(n=10)
self.assertEqual(len(col), 10)
def test_column_unique(self):
col = self.db.tables.Customer.Country.unique()
self.assertEqual(len(col), 24)
def test_table_keys_per_column(self):
short_db = DemoDB(keys_per_column=1)
self.assertEqual("""+----------------------------------------------------------------------------------------+
| Track |
+--------------+---------------+-----------------------+---------------------------------+
| Column | Type | Foreign Keys | Reference Keys |
+--------------+---------------+-----------------------+---------------------------------+
| TrackId | INTEGER | | InvoiceLine.TrackId, (+ 1 more) |
| Name | NVARCHAR(200) | | |
| AlbumId | INTEGER | Album.AlbumId | |
| MediaTypeId | INTEGER | MediaType.MediaTypeId | |
| GenreId | INTEGER | Genre.GenreId | |
| Composer | NVARCHAR(220) | | |
| Milliseconds | INTEGER | | |
| Bytes | INTEGER | | |
| UnitPrice | NUMERIC(10,2) | | |
+--------------+---------------+-----------------------+---------------------------------+""".strip(),
'{0}'.format(short_db.tables.Track.__repr__()).strip())
def tearDown(self):
pass
def test_table_count_rows(self):
count = self.db.tables.Invoice.count
self.assertEqual(count), 412)
def assert_load_same(db: DemoDB, expect: int):
load_db = DemoDB().with_context(db.context).load()
assert load_db.int_val == expect
import pandas as pd
from db import DemoDB, DB, list_profiles, remove_profile
import unittest
db = DemoDB()
class PandaSQLTest(unittest.TestCase):
def setUp(self):
pass
def test_query_rowsum(self):
df = db.query("select * from Artist;")
self.assertEqual(len(df), 275)
def test_query_groupby(self):
q = "select AlbumId, sum(1) from Track group by 1"
df = db.query(q)
self.assertEqual(len(df), 347)
def test_query_from_file_rowsum(self):
with open("/tmp/testscript.sql", "w") as f:
f.write("select * from Artist;")
df = db.query_from_file("/tmp/testscript.sql")
self.assertEqual(len(df), 275)
def test_add_profile(self):
profiles = list_profiles()
db.save_credentials(profile="test_profile")
self.assertEqual(len(profiles)+1, len(list_profiles()))