Note: shillelagh is a drop-in replacement for gsheets-db-api, with many additional features. You should use it instead. If you're using SQLAlchemy all you need to do:
$ pip uninstall gsheetsdb
$ pip install shillelagh
If you're using the DB API:
# from gsheetsdb import connect
from shillelagh.backends.apsw.db import connect
A Python DB API 2.0 for Google Spreadsheets
This module allows you to query Google Spreadsheets using SQL.
Using this spreadsheet as an example:
| A | B | |
|---|---|---|
| 1 | country | cnt |
| 2 | BR | 1 |
| 3 | BR | 3 |
| 4 | IN | 5 |
Here's a simple query using the Python API:
from gsheetsdb import connect
conn = connect()
result = conn.execute("""
SELECT
country
, SUM(cnt)
FROM
"https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/"
GROUP BY
country
""", headers=1)
for row in result:
print(row)
This will print:
Row(country='BR', sum_cnt=4.0)
Row(country='IN', sum_cnt=5.0)
How it works
Transpiling
Google spreadsheets can actually be queried with a very limited SQL API. This module will transpile the SQL query into a simpler query that the API understands. Eg, the query above would be translated to:
SELECT A, SUM(B) GROUP BY A
Processors
In addition to transpiling, this module also provides pre- and post-processors. The pre-processors add more columns to the query, and the post-processors build the actual result from those extra columns. Eg, COUNT(*) is not supported, so the following query:
SELECT COUNT(*) FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/"
Gets translated to:
SELECT COUNT(A), COUNT(B)
And then the maximum count is returned. This assumes that at least one column has no NULLs.
SQLite
When a query can't be expressed, the module will issue a SELECT *, load the data into an in-memory SQLite table, and execute the query in SQLite. This is obviously inneficient, since all data has to be downloaded, but ensures that all queries succeed.
Installation
$ pip install gsheetsdb
$ pip install gsheetsdb[cli] # if you want to use the CLI
$ pip install gsheetsdb[sqlalchemy] # if you want to use it with SQLAlchemy
CLI
The module will install an executable called gsheetsdb:
$ gsheetsdb --headers=1
> SELECT * FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/"
country cnt
--------- -----
BR 1
BR 3
IN 5
> SELECT country, SUM(cnt) FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1
pscv8ZXPtg8/" GROUP BY country
country sum cnt
--------- ---------
BR 4
IN 5
>
SQLAlchemy support
This module provides a SQLAlchemy dialect. You don't need to specify a URL, since the spreadsheet is extracted from the FROM clause:
from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
engine = create_engine('gsheets://')
inspector = inspect(engine)
table = Table(
'https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0',
MetaData(bind=engine),
autoload=True)
query = select([func.count(table.columns.country)], from_obj=table)
print(query.scalar()) # prints 3.0
Alternatively, you can initialize the engine with a "catalog". The catalog is a Google spreadsheet where each row points to another Google spreadsheet, with URL, number of headers and schema as the columns. You can see an example here:
This will make the two spreadsheets above available as "tables" in the default schema.
Authentication
You can access spreadsheets that are shared only within an organization. In order to do this, first create a service account. Make sure you select "Enable G Suite Domain-wide Delegation". Download the key as a JSON file.
Next, you need to manage API client access at https://admin.google.com/${DOMAIN}/AdminHome?chromeless=1#OGX:ManageOauthClients. Add the "Unique ID" from the previous step as the "Client Name", and add https://spreadsheets.google.com/feeds as the scope.
Now, when creating the connection from the DB API or from SQLAlchemy you can point to the JSON file and the user you want to impersonate:
>>> auth = {'service_account_file': '/path/to/certificate.json', 'subject': 'user@domain.com'}
>>> conn = connect(auth)
-
考虑到Java代码一般就是查询hbase存储的hadoop运算的结果数据，下面记录下查询的简单范例代码 package com.lvmama.crm.web.controller.csVipManager; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.hbase.HBaseConfiguration;
-
老样子，我们还是先来了解一下settings.db数据库中system表存放的是什么。 从字面意思我们就可以看出，settings.system为系统数据库，里面存放的数据大多为系统的一些配置数据，包括还有一些应用存放的其他数据信息。 你也可以在项目中把自己的数据存放在系统数据库内，这样就可以达到数据永久存放的目的，即使你的应用被卸载了，这些数据依然不会被删除。大多数我们采用Settings.Sy
-
注意：curl命令示例 这些示例使用curl命令，并遵循以下准则： 使用-X参数指定HTTP动作。 对于GET查询，Accept头设置为text / xml，表示客户端（curl）期望接收以XML格式化的响应。 您可以将其设置为text / json以接收JSON响应。 对于PUT，POST和DELETE，只有带-d参数时发送数据，才应设置Content-Type头。 如果设置了Content-T
-
今天看说学习了下通过java 调用HBase api 实习表的创建和数据插入的操作，贴下代码： package com.lyq.study.api; import java.io.IOException; import java.util.ArrayList; import java.util.List; import org.apache.hadoop.conf.Configuration;
-
对HBASE查询的的API文档按照包含关系整理了一下。 ByteArrayComparable BigDecimalComparator BinaryComparator BinaryPrefixComparator BitComparator LongComparator NullComparator RegexStringComparator SubstringComparator Filte
-
hbase（hadoop）是用java编写的，有些语言（例如python）能够对它提供良好的支持，但也有很多语言使用起来并不是那么方便，比如c#只能通过thrift访问。Rest就能很好的解决这个问题。Hbase的org.apache.hadoop.hbase.rest包提供了rest接口，它内嵌了jetty作为servlet容器。 启动命令：./bin/hbase rest start -p
-
1.配置文件和连接信息 // 两个变量的声明： private static Connection connection = null; private static Admin admin = null; static{ try { // 1.获取配置文件信息 Configuration co
-
项目添加： Microsoft.EntityFrameworkCore.Tools Pomelo.EntityFrameworkCore.MySql (这个是第三方的ef mysql 中间件) Scaffold-DbContext -Force "Server=localhost;User Id=***;Password=****;Database=****" -Provider "Pomelo
-
创建表用Admin； 表查询用Table 一，创建表： public class CreateApi { public static void main(String[] args) throws IOException { Configuration conf = HBaseConfiguration.create(); conf.set("hbase.z
-
db 跟数据库相关的操作。因为各项服务根本上的操作都需要跟数据库打交道，因此这部分定义了大量的数据库资源类和相关接口，可以被进一步继承实现。 包括对核心plugin api的实现基础类，其次是一些扩展的资源和方法的支持。 其中model_base.py和models_v2.py中定义了最基础的几个模型类。
-
基本使用 DB::connection('connection_name'); // 运行数据库查询语句 $results = DB::select('select * from users where id = ?', [1]); $results = DB::select('select * from users where id = :id', ['id' => 1]); // 运行普通语句
-
upper/db upper/db is a productive data access layer (DAL) for Gothat provides agnostic tools to work with different data sources, such as: PostgreSQL MySQL MSSQL CockroachDB MongoDB QL SQLite See uppe
-
null 有人能帮我解决这个问题吗？
-
这是一个小工具用来将 SQL Server 数据库转成 SQLite 数据库。
-
Apache Empire-db 是一个开源的关系型数据持久化组件，能够实现数据库无关的动态查询定义，简便的数据读取和更新。与其它持久化组件相比 如：Hibernate、TopLink、iBATIS或JPA实现，Empire-db更注重编译期类型安全，减少冗余，开发效率的改进。 Empire-db所有的数据库实体都通过动态bean进行管理，因此允许在运行期改变数据模型。