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;"
