pycharm 中sqlite升级方法

sqlite 3.21升级到3.40

Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> help
Type help() for interactive help, or help(object) for help about object.
>>> import sqlite3
>>> print(sqlite3.version_info) #显示sqlite3版本信息
(2, 6, 0)
>>> print(sqlite3.sqlite_version) #显示SQLite版本信息
3.21.0
>>> exit()

升级后

Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> print(sqlite3.version_info) #显示sqlite3版本信息
(2, 6, 0)
>>> print(sqlite3.sqlite_version) #显示SQLite版本信息
3.40.0
>>>

--------------------------------------------------

1.下载sqlite-dll-win64-x64-3400000.zip（注意自己系统版本，我的是win10,64），解压到python的安装目录下C:\Program Files\Python37\DLLs,覆盖原有的sqlite3.dll

在python中测试上面代码，已经升级到3.40

下载地址：SQLite Download Page

2.再找到sqlit-jdbc。从github下载，主页： Releases · xerial/sqlite-jdbc · GitHub

链接地址：sqlite-jdbc-3.40.0.0.jar

3.进入C:\Users\Adminstrator\.PyCharm2019.1\config\jdbc-drivers\Xerial SQLiteJDBC，修改jdbc-drivers.xml文件，找到<artifact version="3.31.1" name="Xerial SQLiteJDBC">小节，根据这一节添加如下新的小节

  <artifact version="3.40.0.0" name="Xerial SQLiteJDBC">
    <item name="sqlite-jdbc-3.40.0.0.jar"
          url="https://repo1.maven.org/maven2/org/xerial/sqlite-jdbc/3.40.0.0/sqlite-jdbc-3.40.0.0.jar"
          md5=""/>
    <item name="license.txt" url="https://cache-redirector.jetbrains.com/download.jetbrains.com/idea/jdbc-drivers/sqlite/license.txt" md5=""/>
  </artifact>

4.再该目录里新建3.40.0.0文件夹，再解压sqlite-jdbc-3.40.0.0.jar到该3.40.0.0文件夹中。

5.重启动pycharm找到sqlite，会自动识别3.40.0.0,再找到项目中sqlite的数据库，会自动提示是否升级到最新驱动，点击升级并测试成功。

为何升级sqlite3?

答：项目相应更新数据，但如果记录不存在，需要插入该记录。该语法要求3.24版本。python7使用的是3.21，所以升级。

update可以 搭配on conflict（原文：sqlalchemy之sqlite3之ON CONFLICT DO UPDATE(insert if exists else update或upsert)_欢乐调包侠的博客-CSDN博客_sqlite on conflict）

网页中有如下文字：SQLite Query Language: upsert https://www.sqlite.org/lang_UPSERT.html

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL. UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04).

------------------------------------------------------------------------------------------------------------ 

在线测试sqlite语法的网页：

SQLite3 Fiddle​​​​​​​​​​​​​​

INSERT INTO phonebook(name,phonenumber) VALUES('Alis','574-334-1212')
  ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;
select * from phonebook