sqlite视图

问题

在Centos上遇到sqlite3版本过低的问题, 3.7., 但是django要求版本不低于3.8., 自己编译升级后, 还是无法解决: 原因是python找寻动态库的位置不对

# 下载并解压
[root@linux ~]# wget https://www.sqlite.org/2019/sqlite-autoconf-3270200.tar.gz
[root@linux ~]# tar xf sqlite-autoconf-3270200.tar.gz
# configure 生成makefile并指定安装路径
[root@linux ~]# cd sqlite-autoconf-3270200
[root@linux sqlite-autoconf-3270200]# ./configure --prefix=/usr/local/
[root@linux sqlite-autoconf-3270200]# make && make install

# 备份旧版本
[root@linux ~]# mv /usr/bin/sqlite3 /usr/bin/sqlite3_backupold
# 链接新版本
[root@linux ~]# ln -s /usr/local/bin/sqlite3 /usr/bin/sqlite3
# 环境变量设置
[root@linux ~]# vim ~/.bashrc

添加内容如下, LD_LIBRARY_PATH 默认是没有设置的, 如果设置了会优先使用

export LD_LIBRARY_PATH="/usr/local/lib"

json1

参考：howto_get_going_with_sqlite_json1

使用版本3.7.17，最低支持版本3.9.0

尝试编译json.so 去3.7.17中.load json.so
- 结论：3.7.17 可以加载，但是运行报错segmentation fault，应该还是有代码不兼容，直接在3.9.0上可加载、可运行

下载：

wget https://www.sqlite.org/2013/sqlite-autoconf-3071700.tar.gz

# sqlite 中查看编译选项 ENABLE_JSON1
pragma compile_options;

使用Makefile生成json1.so，3.38代码已经移动到src/目录

# Makefile
json1.so: json1.lo
	$(LTCOMPILE) -c -fPIC $(TOP)/ext/misc/json1.c
	$(TCC) -shared -o json1.so json1.o

$ make
$ make json1.so

python集成

sqlite-autoconf-3420000.tar.gz，默认支持json1
Python-2.7.5.tar.xz
Python-3.8.6.tar.xz

tar -xf sqlite3-autoconf-3420000.tar.gz
tar -xf Python-2.7.5.tar.xz
tar -xf Python-3.8.6.tar.xz

cd sqlite3-autoconf-3420000
./configure
make
make install
sqlite3 -version

# py2
sudo apt-get install build-essential libsqlite3-dev zlib1g-dev libncurses5-dev libgdbm-dev libbz2-dev libssl-dev libdb-dev
./configure --prefix=/usr --enable-shared --with-ensurepip=install --enable-optimizations
make
make install


# py3
./configure --prefix=/usr --enable-shared --enable-optimizations
make
make install

sqlalchemy slqite json支持

new in version 1.3
pip install sqlalchemy>=1.3

demo

-- test.sql
CREATE table users(id int, data json);
INSERT INTO users (id, data) VALUES (1, json('{"name": "Alice", "age": 25, "email": "alice@example.com"}'));
INSERT INTO users (id, data) VALUES (2, json('{"name": "Bob", "age": 35, "email": "bob@example.com"}'));
INSERT INTO users (id, data) VALUES (3, json('{"name": "Chalice", "age": 17, "email": "chalice@example.com"}'));
INSERT INTO users (id, data) VALUES (4, json('{"name": "David", "age": 80, "email": "david@example.com"}'));

创建测试db

sqlite3 testdb < test.sql

import sqlite3

from sqlalchemy import JSON, Column, Integer, Sequence, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

print(f"sqlite3.version {sqlite3.version}")
print(f"sqlite3.sqlite_version {sqlite3.sqlite_version}")

c = sqlite3.connect("testdb")
cur = c.cursor()
res = cur.execute(
    "select * from users, json_each(users.data) where json_each.key='age' and json_each.value<30;"
)
data = res.fetchall()
print("use json_each and raw query: \n")
print(data)
print(data[1], type(data[1]))


print("use sqlalchemy")
engine = create_engine("sqlite:///testdb", echo=True)

EntityBase = declarative_base()


class Item(EntityBase):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, nullable=False)
    data = Column(JSON, nullable=True)


Session = sessionmaker(bind=engine)
session = Session()

for item in session.query(Item).all():
    print(type(item.data))
    print(item.id, item.data)

(py3) 26huitailang in ~/PycharmProjects/sqlite3-json-demo λ python main.py
sqlite3.version 2.6.0
sqlite3.sqlite_version 3.39.5
use json_each and raw query:

[(1, '{"name":"Alice","age":25,"email":"alice@example.com"}', 'age', 25, 'integer', 25, 4, None, '$.age', '$'), (3, '{"name":"Chalice","age":17,"email":"chalice@example.com"}', 'age', 17, 'integer', 17, 4, None, '$.age', '$')]
(3, '{"name":"Chalice","age":17,"email":"chalice@example.com"}', 'age', 17, 'integer', 17, 4, None, '$.age', '$') <class 'tuple'>
use sqlalchemy
2023-09-13 15:45:14,558 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2023-09-13 15:45:14,558 INFO sqlalchemy.engine.base.Engine ()
2023-09-13 15:45:14,558 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2023-09-13 15:45:14,558 INFO sqlalchemy.engine.base.Engine ()
2023-09-13 15:45:14,559 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2023-09-13 15:45:14,559 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.data AS users_data
FROM users
2023-09-13 15:45:14,559 INFO sqlalchemy.engine.base.Engine ()
<class 'dict'>
1 {'name': 'Alice', 'age': 25, 'email': 'alice@example.com'}
<class 'dict'>
2 {'name': 'Bob', 'age': 35, 'email': 'bob@example.com'}
<class 'dict'>
3 {'name': 'Chalice', 'age': 17, 'email': 'chalice@example.com'}
<class 'dict'>
4 {'name': 'David', 'age': 80, 'email': 'david@example.com'}

问题#

json1#

python集成#

问题

json1

python集成