来源:北北看剧
这两天光大证券(10.570, -0.23, -2.13%)的PE投资在境外亏55亿的事情刷屏了金融圈。
55亿的事情归纪委管,央行大概只会瞄一眼,他们有一件5500亿,哦不,700000亿的事要先处理一下。
5月24日周五,有一则新闻说“包商银行被接管”,金融圈的一些朋友转了,大部分人都没注意。
但你不知道的是,从那一刻起到现在的十天时间里,我们与一波金融危机擦肩而过。
来源:北北看剧
这两天光大证券(10.570, -0.23, -2.13%)的PE投资在境外亏55亿的事情刷屏了金融圈。
55亿的事情归纪委管,央行大概只会瞄一眼,他们有一件5500亿,哦不,700000亿的事要先处理一下。
5月24日周五,有一则新闻说“包商银行被接管”,金融圈的一些朋友转了,大部分人都没注意。
但你不知道的是,从那一刻起到现在的十天时间里,我们与一波金融危机擦肩而过。
very good tutorial
matplotlib和PIL打开图片的方法
|
|
Based on ref matplotlib.pyplot.figure, the default size of a plot is 6.4 and 4.8.
rcParams["figure.figsize"] = [6.4, 4.8]
subplots size
fig, ax1 = plt.subplots(figsize=(20, 10))
如果不用上面的命令,而用ax.figure.set_size_inches(50,10)
,不好使。
other thins
plt.figure(dpi=80, facecolor='w', edgecolor='w')
line
plt.plot(X, C, color="blue", linewidth=1.0, linestyle="-", label="balala data", marker='tri', markersize=3,)
limit (range)
plt.xlim(-4.0,4.0)
legend
plt.legend(loc='upper left')
ticks
plt.xticks(np.linspace(-4,4,9,endpoint=True))
label of axis
plt.xlabel("This is X axis")
save/load
plt.savefig("name.png")
Note that plt.show()
will clean the content, so savefig
should be in front
|
|
histogram
plt.hist(mydict.values(), bins=10)
add additional ticks
|
|
ref: adding extra axis ticks using matplotlib
arrow
plt.arrow(datetime(2018,4,21), 1.268, 30, 0, linestyle=(0,(5,10)))
注意:是(x,y,dx,dy),第三个和第四个参数是箭头的长度;这里30被当做30天;(offset,(onoffseq))格式的参考可见:linestyle
add x2 axis
ax2 = ax1.twinx()
ref: example code
|
|
|
|
gasPrice=w3.eth.gasPrice
|
|
sort=desc
means get the earliest first; sort=asc
verse vise.这是历史性的一个事件:
在遭遇了半夜一点被叫起来强制删帖之后,北大学生将沈X阳事件中自己的遭遇写入了以太坊"input data"中,这意味着这一文件永远无法被删除了!
Etherscan
点击"Convert to ACSII",就可以看到内容。
python cookbook 非常好!
Vultr是老牌的VPS服务商。这里记录一下购买和使用流程。
<head>
element contains meta information about the document<body>
element contains the visible page contentWhat a bubble!
ref: runoob sql
大小写敏感性
有个重要的点值得注意,SQLite 是不区分大小写的,但也有一些命令是大小写敏感的,比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。
注释
SQLite 注释是附加的注释,可以在 SQLite 代码中添加注释以增加其可读性,他们可以出现在任何空白处,包括在表达式内和其他 SQL 语句的中间,但它们不能嵌套。
SQL 注释以两个连续的 “-” 字符(ASCII 0x2d)开始,并扩展至下一个换行符(ASCII 0x0a)或直到输入结束,以先到者为准。
您也可以使用 C 风格的注释,以 “/" 开始,并扩展至下一个 "/” 字符对或直到输入结束,以先到者为准。
The “.databases” command shows a list of all databases open in the current connection. There will always be at least 2. The first one is “main”, the original database opened. The second is “temp”, the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file.
如果导入同一个库两次,.table
会显示什么?
|
|
如果崩溃或断电发生在事务的中间某时刻,则在硬盘上会留有回滚日志。在下次另外一个应用程序尝试打开数据库文件时,它会通知存在回滚日志(“热日志”),并使用日志中的信息来把数据库恢复到未完成事务开始之前的状态。这就是SQLite实现原子提交的基本原理。
临时数据库文件总是在数据库连接关闭时自动删除。
SQLite是无类型的. 这意味着你可以保存任何类型的数据到你所想要保存的任何表的任何列中, 无论这列声明的数据类型是什么(只有自动递增Integer Primary Key才有用). 对于SQLite来说对字段不指定类型是完全有效的.
建表时声明啥类型或者不声明类型都可以,也就意味着创表语句可以这么写:create table if not exists t_student(name, age);
一般数据采用的固定的静态数据类型,而SQLite采用的是动态数据类型,会根据存入值自动判断。SQLite的数据存储类型有: integer (有符号整形), real (浮点值,以8字节IEEE浮点数存放), text(文本字符串,使用数据库编码UTF-8,UTF-16BE或者UTF-16LE存放), blob(是一个数据块,完全按照输入存放; 二进制数据,比如: 文件) 布尔类型 Sqlite没有单独的布尔存储类型,它使用INTEGER作为存储类型,0为false,1为true
IS_OPENING BOOLEAN NOT NULL CHECK (IS_OPENING IN (0,1))
NUMERIC类型:当文本数据被插入到亲缘性为NUMERIC的字段中时,如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据,如果转换失败,SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据,SQLite将不做任何转换,直接以NULL或BLOB的方式存储该数据。需要额外说明的是,对于浮点格式的常量文本,如"30000.0",如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite就会将其转换为INTEGER的存储方式。
Date 与 Time 数据类型
SQLite 没有一个单独的用于存储日期和/或时间的存储类,但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。
虽然可以不写类型,但为了以后方便理解,最好还是写上。
sqlite查询库里所有表名
每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表, 里面存储着数据库的数据结构(表结构、视图结构、索引结构等),只可以对他使用查询语句
SQLITE_MASTER 表DDL信息如下:
CREATE TABLE sqlite_master (type TEXT,name TEXT,tbl_name TEXT,rootpage INTEGER,sql TEXT);
SELECT name FROM sqlite_master WHERE type=’table’ ORDER BY name;
可以参考:SQLite3源码学习(29) 用户事务和Savepoint
通过复制主数据库中的内容到一个临时数据库文件,然后清空主数据库,并从副本中重新载入原始的数据库文件。这消除了空闲页,把表中的数据排列为连续的,另外会清理数据库文件结构。(ct: sqlite是以树结构存储数据的)
NOT NULL 约束:确保某列不能有 NULL 值。
DEFAULT 约束:当某列没有指定值时,为该列提供默认值。
UNIQUE 约束:确保某列中的所有值是不同的。
PRIMARY Key 约束:唯一标识数据库表中的各行/记录。
CHECK 约束:CHECK 约束确保某列中的所有值满足一定条件。
SQLite 支持 ALTER TABLE 的有限子集。重命名列,删除一列,或从一个表中添加或删除约束都是不可能的!!!
你不能从Python中调用点命令,因为点命令是特定于命令行shell工具的。
.schema table_name
: 列出创建表的语句。.sch
也可以.tables
: 列出所有表名.headers on
列出表头(column names)$sqlite3 testDB.db .dump > testDB.sql
|
|
The ATTACH DATABASE statement adds another database file to the current database connection. Database files that were previously attached can be removed using the DETACH DATABASE command.
SQLite 视图是只读的,因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器,当尝试 DELETE、INSERT 或 UPDATE 视图时触发,需要做的动作在触发器内容中定义。
最简单的视图:
CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col > 100;
创建临时视图:
sqlite> CREATE TEMP VIEW tempview AS SELECT * FROM testtable WHERE first_col > 100;
“IF NOT EXISTS”
sqlite> CREATE VIEW IF NOT EXISTS testview AS SELECT * FROM testtable WHERE first_col > 100;
删除视图
sqlite> DROP VIEW IF EXISTS testview;
只有两个用法:
ALTER TABLE database_name.table_name RENAME TO new_table_name;
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
日期表达式
返回当前系统日期和时间值,注意是UTC+0时间
SELECT CURRENT_TIMESTAMP;
例子[3]
|
|
|
|
参考:
Update multiple rows with different values in a single SQL query
sqlite update 多行
|
|
GLOB大小写敏感;LIKE大小写不敏感
|
|
security[5]
Use ?
as a placeholder!
Use Python’s string operations to assemble a query makes program vulnerable to an SQL injection attack
if connection is not closed
When the connection is closed explicitly by code or implicitly by program exit then any outstanding transaction is rolled back.
Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed.
|
|
FIRST OF ALL: There’re two distinct ‘cursor’ concept:
In standard SQL, it’s like concept of iterator[6]:
In computer science and technology, a database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.
In sqlite module of python, it’s basically the same with connection. Or say, connection is a shortcut of cursor (make things easier).[6]
Connection.execute
This is a nonstandard shortcut that creates a cursor object by calling the cursor() method, calls the cursor’s execute() method with the parameters given, and returns the cursor.
As long as you use the standard methods of the Cursor object, you can be sure that if you switch to another database implementation that follows the above specification, your code will be fully portable. Perhaps you will only need to change the import line.
Cursor objects allow you to keep track of which result set is which, since it’s possible to run multiple queries before you’re done fetching the results of the first.
ref: how often do I have to commit?
What concurrent readers see
When a change to the database is committed, it becomes visible for other connections. Unless it is committed, it remains visible only locally for the connection to which the change was done. Because of the limited concurrency features of sqlite, the database can only be read while a transaction is open.
balance between Performance and Exclusively locking the database
Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second.
sqlite3 in python
By default, the ‘isolation_level’ parameter of a connection is set as DEFERRED.
Set ‘isolation_level’ to ‘None’, this will leave the underlying sqlite3 library operating in autocommit mode. (autocommit mode means that statements that modify the database take effect immediately.)
for more about read datetime.datetime or even adapter, see doc: DB-API 2.0 interface for SQLite databases
建立connect时加上detect_types=sqlite3.PARSE_DECLTYPES
, 就可以直接读写datetime类型:
|
|
simple example
read & write boolean directly:
|
|
define __conform__
in your class
|
|
register an adapter callable
|
|
datatime.date and datetime.datetime can be directly written to sqlite3
sqlite3 module has two default adapters for Python’s built-in datetime.date and datetime.datetime types.
cur.execute("PRAGMA table_info('my_table_name')").fetchall()
返回值是a list of tuple; tuple分别含有下列元素:
cid name type notnull dflt_value pk
e.g.:
[(0, 'ORDER_IN_PAIR_UTCTIME', 'TIMESTAMP', 1, None, 0)]
First of all, dot command is just for sql shell.
.schema
– corresponding to PRAGMAHow to name an SQLite database so it doesn’t have the default name of main? ↩
Why do you need to create a cursor when querying a sqlite database? ↩ ↩ ↩
How to read datetime back from sqlite as a datetime instead of string in Python? ↩
Using adapters to store additional Python types in SQLite databases ↩