Skip to content. | Skip to navigation

Personal tools

Navigation

You are here: Home / Tips / SQL Connection

SQL Connection

常見的方式是利用 Database Adapter 來連接，再透過 Z SQL Method 當介面來傳遞參數存取。 https://medium.com/@katemarielewis/grouping-and-aggregating-data-using-sql-7ac85d654fe4 Prisma

過往常見的方式，是根據不同的 SQL 資料庫，使用不同的 Database Adapter，例如 Products.ZMySQLDA 是用來連接 MySQL 的模組，再透過 Z SQL Method 當介面來傳遞參數存取。新式的作法，則改用 MySQL-python 來連接資料庫，再利用 z3c.saconfig 管理 Session 設定。

Spark SQL Cookbook Openlink Virtuoso Postgresql Adding Foreign Keys with Zero Downtime

Apache Arrow

move fast and migrate things how we automated Migrations in Postgres Transaction Isolation

MySQL

conda search mysql 回傳 mysql-connector-python 和 pymysql 並沒有 MySQLdb，另外 mysql-python 套件只針對 Python 2.7 環境打包，在 Python 3.x 可能遇到 PackageNotFoundError 錯誤:

$ conda install mysql-python
Fetching package metadata .........

PackageNotFoundError: Package missing in current osx-64 channels:
  - mysql-python

Close matches found; did you mean one of these?

    mysql-python: mysql-connector-python

MySQLdb

安裝 MySQL-python 的方式，以 buildout 的 develop.cfg 為例，是指定 eggs 參數內容：

eggs +=
    MySQL-python

預設可以在 buildout-cache/eggs/MySQL_python-1.2.5-py2.7-linux-i686.egg/ 目錄，找到安裝好的檔案，簡單的測試方法如下：

$ bin/plonectl debug
>>> import _mysql
>>> db=_mysql.connect(passwd="topsecret",db="mydb")

以上是推薦的方法，接著可考慮透過 Python Script 來完成設定。傳統但不再被維護的方式是，搭配 Products.ZMySQLDA 安裝步驟是: 確認 MySQL Server 執行, 具備合適帳號權限來存取, Z MySQL Database Connection

pip 和 Anaconda 安裝 MySQL 方法

collective.recipe.zmysqlda zest.recipe.mysql

連接範例 高效能的設定方式

EnvironmentError: mysql_config not found

$ sudo apt-get install libmysqlclient-dev

ZMySQLDA: apt-get install libmysql++-dev

# MySQL-Python

eggs =
    MySQL-Python

# ZMySQLDA

urls = http://www.zope.org/Members/adustman/Products/ZMySQLDA/2.0.8/ZMySQLDA-2.0.8.tar.gz

[buildout]
parts =
    ...
    zmysqlda

[zmysqlda]
recipe = cns.recipe.zmysqlda
target = ${buildout:directory}/products

遇到 ImportError: No module named ImageFile 錯誤，修改 DA.py 和 DABase.py 就行：

from App.ImageFile import ImageFile

DBAdapterFolder_icon.gif 從 Zope2-2.13.23-py2.7.egg/Shared/DC/ZRDB/www 搬到 Products.ZSQLMethods-2.13.4-py2.7.egg/Shared/DC/ZRDB/www/

多層目錄一次查詢 8.0 取消 Query Cache

Using MySQL in Plone by Scott Beardsley: 1) Insure Server Mode, Add New User for Zope, Test Database Access 2) Add Z MySQL DB Connection in ZMI, Connect String: mydb zope zope 3) Method + Page Template

<tal:def define="people python:context.get_people(type=sname,active='yes',entity=1)">
<tal:block repeat="person people">

First DB to Implement CATS

MS SQL

Django: 要再安裝 pywin32

Control Panel

collective.saconnect: simple control panel and storage for SQLAlchemy connection strings

z3c.saconfig: supports in-ZODB configuration through SiteScopedSession; need to provide your own subclass of that with its own user interface, and make sure you install this as a local utility.

從 collective.lead 移植到 z3c.saconfig Session 機制會自行管理資料庫連結狀況

<configure xmlns="http://namespaces.zope.org/zope"
    xmlns:db="http://namespaces.zope.org/db">
  <include package="z3c.saconfig" file="meta.zcml" />

  <db:engine name="dbmyapp" url="oracle://user:pass@hostname:port/sid" />
  <db:session name="dbmyapp" engine="dbmyapp" />
</configure>

cx_Oracle IBM DB2 NullPoll vs QueuePoll

dj-database-url -> collective.recipe.database_url -> Plock + Heroku

Windows + Plone 4.0.4

via SSH Tunnel

Build SSH runnel by running:

ssh login@server -L 3306:127.0.0.1:3306 -N

Stop the command and set it in background. Connect to MySQL server as if it is running on localhost:3306 :

mysql -h 127.0.0.1 -p -u user database

When finishing, press fg to get it into foreground.

Incremental Backup

ZODB

http://glicksoftware.com/blog/inspect-your-zodb-with-eye

Why SQL Storage is Hard

傳統上 Archetypes 內容預設儲存在 ZODB 裡，想要使用 SQL Database 當作後端並不是簡單的事，早期的 SQLStorage 方案並不成熟，後續再有 collective.tin 和 ContentMirror 的方案出現。

There are two sources to the complexity. 首先 Plone/CMF/Zope2 預設 Content 來自 ZODB 因此繼承了特定的 Base Class，這項前提必須先被處理，所幸新一代的開發成果已讓這種調整方向變得容易。 The other source of complexity is that it was built for a project that needed versioning and parent-child relations between tables, which requires more complex logic than simply turning a table into a single level folder of content objects. It still requires several weeks of work to bring it up to date (probably including changes to use Dexterity rather than plone.app.content base types) and simplify things. At this point this is not recommend unless you had a very good understanding of SQLAlchemy and the various Plone/CMF/Zope2 layers.

ContentMirror

http://pypi.python.org/pypi/ore.contentmirror

collective.dexteritycontentmirror

RelStorage

http://pypi.python.org/pypi/RelStorage

Shane Hathaway: Plone3 + Relstorage + MySQL change to plone.recipe.zope2instance 取代 file-storage 區段

rel-storage =
  type postgresql
  dbname zodb
  user postgres
  host localhost
  password postgres

Ubuntu libpq-dev 可能要檢查

Plone4 base.cfg

[zopepy]
  recipe = zc.recipe.egg
  eggs = ${buildout:eggs}
  interpreter = zopepy
  scripts = zopepy zodbconvert

效能問題

SQLAlchemy

uncommitted transaction

PyODBC

Peewee zope.sqlalchemy update

python2.7/dist-packages/MySQLdb/cursors.py:206: Warning: Data truncated for column '事件-文本內容' at row 1

Products.SQLAlchemyDA is supposed to work as a (drop-in) replacement für ZPsycopgDA

PostgreSQL有一直在進步當中，但他其實是"Object" relational database，如果以後有轉DB回去的話，設計schema時要小心一點

Filed under: