def test_datetime_query(pdsql, db_flavor): meat = load_meat() expected = meat[meat['date'] >= '2012-01-01'].reset_index(drop=True) result = pdsql("SELECT * FROM meat WHERE date >= '2012-01-01'") if db_flavor == 'sqlite': # sqlite uses strings instead of datetimes pdtest.assert_frame_equal(expected.drop('date', 1), result.drop('date', 1)) else: pdtest.assert_frame_equal(expected, result)
from sklearn.datasets import load_iris import pandas as pd from pandasql import sqldf from pandasql import load_meat, load_births import re births = load_births() meat = load_meat() iris = load_iris() iris_df = pd.DataFrame(iris.data, columns=iris.feature_names) iris_df['species'] = pd.Categorical.from_codes(iris.target, iris.target_names) iris_df.columns = [re.sub("[() ]", "", col) for col in iris_df.columns] dataset = pd.read_csv('c:/temp/data-small.txt', sep='\t') print(sqldf("SELECT * FROM iris_df LIMIT 10;", locals())) print(sqldf("SELECT sepalwidthcm, species FROM iris_df LIMIT 10;", locals())) print(sqldf("SELECT * FROM dataset LIMIT 10;", locals()))
__FILENAME__ = demo from sklearn.datasets import load_iris import pandas as pd from pandasql import sqldf from pandasql import load_meat, load_births import re births = load_births() meat = load_meat() iris = load_iris() iris_df = pd.DataFrame(iris.data, columns=iris.feature_names) iris_df["species"] = pd.Categorical(iris.target, levels=iris.target_names) iris_df.columns = [re.sub("[() ]", "", col) for col in iris_df.columns] print sqldf("select * from iris_df limit 10;", locals()) print sqldf("select sepalwidthcm, species from iris_df limit 10;", locals()) q = """ select species , avg(sepalwidthcm) , min(sepalwidthcm) , max(sepalwidthcm) from iris_df group by species; """ print "*" * 80
from pandasql import sqldf, load_meat, load_births pysqldf = lambda q: sqldf(q, globals()) meats = load_meat() births = load_births() meats['idx'] = meats.index births['idx'] = births.index q = """SELECT m.idx as midx, b.idx as bidx FROM meats m INNER JOIN births b ON m.date = b.date;""" # print(pysqldf(q).head()) import pandas as pd import numpy as np import smv.DataDict as DataDict EXEC = 0 EXIT = 1 WAKEUP = 2 BLOCK = 4 TICK = 10 ENQ = 13 def dummy_data():
def test_returning_single(pdsql): meat = load_meat() result = pdsql("SELECT beef FROM meat LIMIT 10") assert len(result) == 10 pdtest.assert_frame_equal(meat[['beef']].head(10), result)
def test_returning_none(self): pysqldf = lambda q: sqldf(q, globals()) meat = load_meat() result = sqldf("SELECT beef FROM meat LIMIT 10;", locals()) self.assertEqual(len(result), 10)
def test_datetime_query(self): pysqldf = lambda q: sqldf(q, globals()) meat = load_meat() result = sqldf("SELECT * FROM meat LIMIT 10;", locals()) self.assertEqual(len(result), 10)
import pandasql as psql meat = psql.load_meat() print(meat.head()) sdfc = psql.sqldf('select pork, veal, beef from meat') print(sdfc.head()) a = psql.sqldf('select * from meat where beef>700') print(a)
# -*- coding: utf-8 -*- """ Created on Sun Oct 4 10:08:24 2020 @author: chris.cirelli """ from pandasql import sqldf, load_meat pysqldf = lambda q: sqldf(q, globals()) data_meat = load_meat() test = pysqldf("SELECT beef from data_meat LIMIT 5;") print(test)
def main(): # Data meat = pdsql.load_meat() products = pd.read_csv('Products.csv', sep='\s+', lineterminator='\r') print(products)
Primary Keys Create or drop primary keys Indexes Create and drop indexes (performance tuning) SQL Data Types Data Types Data Types in SQL SQL Programming Comments How to create comments within your SQL statement """ # Imports import pandasql as pdsql import pandas as pd import numpy as np meat = pdsql.load_meat() products = pd.read_csv('Products.csv', sep='\s+', lineterminator='\r') def main(): # Data meat = pdsql.load_meat() products = pd.read_csv('Products.csv', sep='\s+', lineterminator='\r') print(products) # queries #q = "select beef,veal from meat;" #q = "select * from meat where beef>2450 and veal>10;" #q = "select * from meat where beef>2450 and veal>10 order by lamb_and_mutton;" #q = "select * from meat limit 3;"