Python peewee experience, pythonpeewee

Source: Internet
Author: User

Python peewee experience, pythonpeewee

The use case in this article is based on python2.7.

The following content is personal peewee experience and pitfalls, and does not involve too many basic operations. Therefore, if you have not used peewee, read the document first.

Correctness and coverage need to be improved. If you have any new questions, please discuss them.

I. Introduction

Peewee is a simple and lightweight Python ORM.

  1. Simple, lightweight, and expressive ORM
  2. Support python version 2.6 + and 3.2 +
  3. Supported databases include sqlite, mysql, and postgresql.
  4. Include a bunch of practical extensions in the playhouse Module

All in all, peewee can fully cope with the Model layer of Small and Medium Projects for individuals or enterprises. It is easy to get started and has powerful functions.

Ii. Basic usage

from peewee import *db = SqliteDatabase('people.db')class BaseModel(Model):  class Meta:    database = db # This model uses the "people.db" database.class Person(BaseModel):  name = CharField()  birthday = DateField()  is_relative = BooleanField()  

For basic usage, read the documentation -- quickstart.

3. Recommended posture

The following describes some of my experience and difficulties in using peewee, hoping to help you better use peewee.

3.1 connect to the database

We recommend that you use the db_url module in playhouse when connecting to the database. The connect Method of db_url can generate a database connection through the input URL string.

3.1.1 connect (url, ** connect_params)

Creates a database instance using the input url string

Url format:

  1. Mysql: // user: passwd @ ip: port/my_db will create a local MySQL my_db database instance (will create a MySQLDatabase instance)
  2. Mysql + pool: // user: passwd @ ip: port/my_db? Charset = utf8 & max_connections = 20 & stale_timeout = 300 create a connection pool for my_db of local MySQL. the maximum number of connections is 20 (In a multi-threaded application, up to max_connections will be opened. each thread (or, if using gevent, greenlet) will have it's own connection .), the timeout value is 300 seconds (will create a PooledMySQLDatabase instance)

Note: charset is utf8 by default. If you need to support emoji, set charset to utf8mb4, and ensure that the character set is correctly set during database creation.CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;。

Supported schemes:

  1. Apsw: APSWDatabase
  2. Mysql: MySQLDatabase
  3. Mysql + pool: PooledMySQLDatabase
  4. S: PostgresqlDatabase
  5. Postgres + pool: PooledPostgresqlDatabase
  6. Eclipsext: PostgresqlExtDatabase
  7. Eclipsext + pool: pooled1_sqlextdatabase
  8. Sqlite: SqliteDatabase
  9. Sqliteext: SqliteExtDatabase
  10. Sqlite + pool: PooledSqliteDatabase
  11. Sqliteext + pool: PooledSqliteExtDatabase

3.1.2 recommended posture

from playhouse.db_url import connectfrom dock.common import config# url: mysql+pool://root:root@127.0.0.1:3306/appmanage?max_connections=300&stale_timeout=300mysql_config_url = config_dict.get('config').get('mysql').get('url')db = connect(url=mysql_config_url)

For more details, see the official documentation db-url.

3.2 use of Connection Pool

When using the peewee connection pool, you must explicitly close the connection. Next, let's explain why, and finally give a recommended method to avoid pitfalls.

3.2.1 why explicitly close the connection

Connections will not be closed exactly when they exceed their stale_timeout. Instead, stale connections are only closed when a new connection is requested.

The following is a reference to the official documentation. Generally speaking, "timeout connections will not be automatically closed, but will only be closed when new requests exist ". The request here refers to the 'request processed by the web Framework ', the source code segment of peewee:

Def _ connect (self, * args, ** kwargs): while True: try: # Remove the oldest connection from the heap. ts, conn = heapq. heappop (self. _ connections) # _ connections is the list (pool) key = self. conn_key (conn) Counter t IndexError: ts = conn = None logger. debug ('no connection available in pool. ') break else: if self. _ is_closed (key, conn): # This connecton was closed, but since it was not stale # it got added back to the queue of available conns. we # then closed it and marked it as explicitly closed, so # it's safe to throw it away now. # (Because Database. close () CILS Database. _ close ()). logger. debug ('Connection % s was closed. ', key) ts = conn = None self. _ closed. discard (key) elif self. stale_timeout and self. _ is_stale (ts): # If we are attempting to check out a stale connection, # then close it. we don't need to mark it in the "closed" # set, because it is not in the list of available conns # anymore. logger. debug ('Connection % s was stale, closing. ', key) self. _ close (conn, True) self. _ closed. discard (key) ts = conn = None else: break if conn is None: if self. max_connections and (len (self. _ in_use)> = self. max_connections): raise ValueError ('exceeded maximum connections. ') conn = super (PooledDatabase, self ). _ connect (* args, ** kwargs) ts = time. time () key = self. conn_key (conn) logger. debug ('created new connection % s. ', key) self. _ in_use [key] = ts # connect to the instance dict return conn in use

According to_connect The method code shows that each time a database connection is established, the system checks whether the connection to the instance times out. Note that the database connection instance in use (the database connection instance in _ in_use dict) does not check whether the database connection times out during database creation.

Because in this Code, every time you create a connection instance_connections(Pool) to obtain the instance. If there is any, it determines whether the instance has timed out. If not, it creates a new instance.

However, the database connection in use is not _connections So every time you create a database connection instance, the system does not check whether the database connection instance in use times out.

Only_close Method. After this method is executed, the connected instance will be put back _connections (Pool ).

Def _ close (self, conn, close_conn = False): key = self. conn_key (conn) if close_conn: self. _ closed. add (key) super (PooledDatabase, self ). _ close (conn) # method for closing database connection elif key in self. _ in_use: ts = self. _ in_use [key] del self. _ in_use [key] if self. stale_timeout and self. _ is_stale (ts): # The Connection instance in _ in_use times out logger. debug ('closing stale connection % s. ', key) super (PooledDatabase, self ). _ close (conn) # Shut down the database connection when timeout. else: logger. debug ('returning % s to pool. ', key) heapq. heappush (self. _ connections, (ts, conn) # Put it back to the pool without timeout

3.2.2 problems that may occur if the connection is not explicitly closed

If you do not call_closeThe database connection after use will not be closed (two meanings: Return to the pool and close the database connection), which will cause two problems:

1. Each time a database connection is created, because no database connection instance is available in the pool. It will result in a slight amount of concurrency and will returnExceeded maximum connections.Error

2. mySQL also has timeout. If a connection has not been requested for a long time, MySQL Server will close the connection. However, peewee has been established (which will be explained later in this article) the connection instance does not know that the MySQL Server is closed. If you request data through this connection, the system will returnError 2006: “MySQL server has gone away"Error, according to the official documentation

3.2.3 recommended posture

Therefore, the connection to the instance is closed after each operation.

Usage 1: Use

def send_rule():  with db.execution_context():  # A new connection will be opened or, if using a connection pool,  # pulled from the pool of available connections. Additionally, a  # transaction will be started.    for user in get_all_user():      user_id = user['id']      rule = Rule(user_id)      rule_dict = rule.slack_rule(index)      .....do something.....

Usage 2: Use Flask hook

@ App. before_requestdef _ db_connect (): database. connect () # This hook ensures that the connection is closed when we 've ve finished # processing the request.@app.teardown _ requestdef _ db_close (exc): if not database. is_closed (): database. close () ### more elegant usage: from playhouse. flask_utils import FlaskDBfrom dock_fastgear.model.base import db # app = Flask (_ name _) FlaskDB (app, db) # in this way, the above operations are automatically performed (for specific implementations, refer to http: // docs. Peewee-orm.com/en/latest/peewee/playhouse.html? Highlight = Flask % 20DB # flask-utils)

For more details, see the official document "pool-apis ".

3.3 Process query results

There are no major pitfalls here, that is, there are two points to note:

First, the query results are all the objects of the Model. Note that they are not dict. If you want the result to be dict, you mustplayhouse Module tool method conversion:from playhouse.shortcuts import model_to_dict

Second,getThe method returns only one record.

3.3.1 recommended posture

from playhouse.shortcuts import model_to_dictfrom model import HelloGitHubdef read_from_db(input_vol):  content_list = []  category_object_list = HelloGitHub.select(HelloGitHub.category).where(HelloGitHub.vol == input_vol)\    .group_by(HelloGitHub.category).order_by(HelloGitHub.category)  for fi_category_object in category_object_list:    hellogithub = HelloGitHub.select()\      .where((HelloGitHub.vol == input_vol)          & (HelloGitHub.category == fi_category_object.category))\      .order_by(HelloGitHub.create_time)    for fi_hellogithub in hellogithub:      content_list.append(model_to_dict(fi_hellogithub))  return content_list

Iv. Common Errors and Solutions

4.1 'buffer' object has no attribute 'translate'

  1. Error message: "'buffer' object has no attribute 'translate '"
  2. Scenario: The BlobField stores zlib compress compressed data
  3. Solution: You must specify that the pymysql version is earlier than 0.6.7. Otherwise, an error is returned.
  4. Reference

4.2 Can't connect to MySQL server Lost connection to MySQL server during query

  1. Error message: Can't connect to MySQL server Lost connection to MySQL server during query
  2. Scenario: insert data into RDS
  3. Solution: because the number of requests exceeds the number of connections set by RDS, you need to increase the number of RDS connections.
  4. Reference

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.