Postgresql Notes

如何生成并插入测试数据? 比如说我有个表,结构如 sql:create table test (time timestamp, value double precision, sensor integer),想插入一些测试的数据,可以用 postgresql 的 generate_series 函数。 下面这条 sql 会一次插入 5000 条。 insert into test (time, sensor, value) select now(), i, random() from generate_series(1, 5000) s(i) 如何查看数据占用磁盘大小? 查看表的大小 select pg_size_pretty(pg_relation_size('pressure_01')) 查看数据库的大小 select pg_size_pretty(pg_database_size('pressure_01'))

Use django-filter to add advanced filter for rest api

在上一篇文章中,我们介绍了 django-filter 的基本功能。实际上 django-filter 还可以实现更高级的搜索,比如说文本的模糊查询,日期的范围查询。 继续我们的例子: class Product(models.Model): name = models.CharField(max_length=100) created_at = models.DatetimeField() class ProductSerializer(serializers.ModelSerializer): class Meta: fields = '__all__' class ProductFilter(django_filters.FilterSet): sort = django_filters.OrderingFilter(fields=('created_at',)) class Meta: model = Product fields = ['name',] class ProductListView(generics.ListView): queryset = Product.objects.all() serializer_class = ProductSerializer filter_backends = (DjangoFilterBackend,) filter_class = ProductFilter 首先要实现按 name 的模糊查询。上面 ProductFilter 里面的 fields,如果是 list,默认就是按=来匹配。它还支持 dict 的格式,可以选择更多的匹配方式。按 name 的模糊匹配,只需把 fields 改成如下即可: fields = { 'name': ['icontains'] } 使用 API 查询是原来的 url 是/products/, 模糊查询的 url 就变成/products/?

Use django-filter to add filter for rest api

对一个 list api 来说,查询和排序都是基本的需求,使用 django-filter 可以在 DRF 中用很少的代码满足我们的需求。 比如说如下的 model: class Product(models.Model): name = models.CharField(max_length=100) created_at = models.DatetimeField() 我们用 DRF 的 generic view 可以创建一个 list view:首先需要写一个 serializer: class ProductSerializer(serializers.ModelSerializer): class Meta: fields = '__all__' class ProductListView(generics.ListView): queryset = Product.objects.all() serializer_class = ProductSerializer 如果不使用 django-filter,可以覆盖 ProductListView 的 get_queryset 来实现 filter 功能,比如说使用 name 做 filter: def get_queryset(self): queryset = Product.objects.all() name = self.request.query_params.get('name', None) if name is not None: queryset = queryset.

Pandas in jupyter

Read data from mysql and draw a chart. %matplotlib inline import pymysql import pandas as pd import pandas.io.sql as sql conn = pymysql.connect(host='192.168.56.1', user='root', passwd='qwer-1235', db='pdss') s = 'select number_value from quality_data_item_record where item_id = 11' df = sql.read_sql_query(s, conn) df.plot()

Javascript library notes

Some notes on a few popular javascript libraries. bootstrap-daterangepicker How to get startDate and endDate? var range = $("#range").daterangepicker(); var start = range.data("daterangepicker").startDate; var end = range.data("daterangepicker").endDate; lodash map var data = [{label: '4/1', value: 10}, {label: '4/2', value: 14}]; var labels = _.map(data, function(item) { return item.label; }); var values = _.map(data, function(item) { return item.value; }); It is especially useful when preparing data for some chart libraries.

Book reading record 2017

Read 夜色人生 Software Prefactoring How to win friends and influence people Eat, pray, love 猎豹 Great gatsby 四步创业法 To kill a mocking bird Then there were none 你应该像喜欢苦一样喜欢甜 清醒思考的艺术 垂暮之战 幽灵旅 消失的殖民地 东方快车谋杀案 Reading Getting to YES Inspired, how to create products customers love On managing yourself Software architecture for developers To be read Redis In Action Alice in wonder world 身份的焦虑 简单的哲学 深入浅出数据分析 演讲之禅

Introduction

Try to use hugo to setup the blog again.

Dynamic form with springmvc and thymeleaf

Requirements SubjectGroup and SubjectGroupOption has one-to-many relationship. When adding a subject group, subject group options can be added inline. Things I’ve learned. When adding an option, don’t need to use javascript to handle the added html snippet. Just submit the add request to controller and handle the adding in the controller (Add a new options into the option list of subject group), then return to the add page. Spring mvc’s data binding and thyme leaf’s for loop will handle the html.