a small, expressive orm -- supports postgresql, mysql and sqlite

Overview

http://media.charlesleifer.com/blog/photos/peewee3-logo.png

peewee

Peewee is a simple and small ORM. It has few (but expressive) concepts, making it easy to learn and intuitive to use.

  • a small, expressive ORM
  • python 2.7+ and 3.4+ (developed with 3.6)
  • supports sqlite, mysql, postgresql and cockroachdb
  • tons of extensions
https://travis-ci.org/coleifer/peewee.svg?branch=master

New to peewee? These may help:

Examples

Defining models is similar to Django or SQLAlchemy:

from peewee import *
import datetime


db = SqliteDatabase('my_database.db')

class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    username = CharField(unique=True)

class Tweet(BaseModel):
    user = ForeignKeyField(User, backref='tweets')
    message = TextField()
    created_date = DateTimeField(default=datetime.datetime.now)
    is_published = BooleanField(default=True)

Connect to the database and create tables:

db.connect()
db.create_tables([User, Tweet])

Create a few rows:

charlie = User.create(username='charlie')
huey = User(username='huey')
huey.save()

# No need to set `is_published` or `created_date` since they
# will just use the default values we specified.
Tweet.create(user=charlie, message='My first tweet')

Queries are expressive and composable:

# A simple query selecting a user.
User.get(User.username == 'charlie')

# Get tweets created by one of several users.
usernames = ['charlie', 'huey', 'mickey']
users = User.select().where(User.username.in_(usernames))
tweets = Tweet.select().where(Tweet.user.in_(users))

# We could accomplish the same using a JOIN:
tweets = (Tweet
          .select()
          .join(User)
          .where(User.username.in_(usernames)))

# How many tweets were published today?
tweets_today = (Tweet
                .select()
                .where(
                    (Tweet.created_date >= datetime.date.today()) &
                    (Tweet.is_published == True))
                .count())

# Paginate the user table and show me page 3 (users 41-60).
User.select().order_by(User.username).paginate(3, 20)

# Order users by the number of tweets they've created:
tweet_ct = fn.Count(Tweet.id)
users = (User
         .select(User, tweet_ct.alias('ct'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User)
         .order_by(tweet_ct.desc()))

# Do an atomic update
Counter.update(count=Counter.count + 1).where(Counter.url == request.url)

Check out the example twitter app.

Learning more

Check the documentation for more examples.

Specific question? Come hang out in the #peewee channel on irc.freenode.net, or post to the mailing list, http://groups.google.com/group/peewee-orm . If you would like to report a bug, create a new issue on GitHub.

Still want more info?

http://media.charlesleifer.com/blog/photos/wat.jpg

I've written a number of blog posts about building applications and web-services with peewee (and usually Flask). If you'd like to see some real-life applications that use peewee, the following resources may be useful:

Comments
  • Sqlite database locked - regression introduced in 2.4.5?

    Sqlite database locked - regression introduced in 2.4.5?

    Below an excerpt from my CherryPy app.

    The app receives bursts of ajax calls to cncprogram_done. Sometimes just 1 or 2 calls, sometimes up to 10 or 20 at a time (I will eventually optimize it, but for now I'm OK with it).

    I just upgraded to Peewee 2.8.3 and only the first call of each burst works, the other calls say peewee.OperationalError: database is locked while executing part.save().

    I tried to revert to older versions, and I found out that with Peewee 2.4.4 works well and with the 2.4.5 fails.

    I reverted back to 2.4.4 on the production server and everything seems to work fine.

    Any idea whether I am doing something wrong or this is really a regression? Where do I start investigating?

    Here is (part of) the code:

    db = peewee.SqliteDatabase(path_name + '/doc.db', threadlocals=True)
    
    class PeeweeModel(peewee.Model):
        class Meta:
            database = db
    
    class CncProgramPart(PeeweeModel):
        sheet = peewee.ForeignKeyField(CncProgramSheet, related_name='parts')
        part_number = peewee.CharField()
        time_finished = peewee.DateTimeField(default=0)
        remake = peewee.BooleanField(default=False)
        comment = peewee.CharField(default='')
    
        def render(self, settings):
            return render('cncprogram_edit_part_row.html',
                          {'part': self, 'settings': settings})
    
    class DocFinder:
    
        @cherrypy.expose
        def cncprogram_done(self, rowid, value):
    
            with db.transaction():
                checked = value == 'true'
                now = time.time()
                part = CncProgramPart.get(CncProgramPart.id == rowid[9:])
    
                if checked and not part.remake and not part.comment:
                    part.time_finished = now
                    part.comment = ''
                    part.remake = False
                    part.save()
                elif part.time_finished:
                    part.time_finished = 0
                    part.save()
    
                return part.render(Settings())
    
    opened by stenci 40
  • Documentation Improvement - Master Issue

    Documentation Improvement - Master Issue

    The purpose of this issue is to collect suggestions for how the documentation might be improved.

    Some of the things I'm wondering:

    • Is it easy to find the information you're looking for?
    • Does the organization of the sections and subsections make it easy to find what you need?
    • Are some sections too verbose? Too terse?
    • Do we need more examples?
    • Is there something you frequently find that you have to look-up or are confused by?
    opened by coleifer 31
  • Losing MySQL connections even though using Pool etc.

    Losing MySQL connections even though using Pool etc.

    Hi, so I'm writing a Python Thrift service and use Peewee as my ORM. Our DB is on a MySQL server. I've had many different problems with the "MySQL server going away" and googling it I found these two tips: a) Use the playhouse connection pool b) After connecting, call database.get_conn().ping(True) (though I feel like I need to add that inside the pool code?)

    So right now I'm using a pool both for write-connections as well as the read-slaves. I experimented with a bunch of different values for the pool parameters, but at the moment I am at:

    max_connections=100, connect_timeout=20, stale_timeout=60, threadlocals=True
    

    Problem is, I still regularly get into the position where the connection goes away during an operation, or in between operations... Which would be fine, but in that case Peewee is failing completely to reconnect, even though that is exactly what the pool should do? E.g. this morning I got a "OperationalError: (2006, "MySQL server has gone away (error(32, 'Broken pipe'))")" when trying to run something on my service, and, alarmingly, it did not work even when sending new requests, i.e. Peewee did not even try to reconnect. The MySQL server was all up and running though and simply restarting the service fixed it.

    From time to time I will also get a "OperationalError: (2013, "Lost connection to MySQL server during query (timeout('timed out',))")", which is probably some server issue, but I'd really really like Peewee to reconnect automatically so that I don't have to manually "try except retry" every single Peewee command I run.

    Do you have any help for me how I can make definitely sure that my queries don't fail, and if they do, how to reconnect? Also, how could that "Server gone away" happen, even after resending the query? Below I attached two example stack traces for exceptions.

    Thanks, Max

      File "/var/www/model.py", line 216, in find_all_general
        total = query.select(Resource).count()
      File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2366, in count
        return self.wrapped_count(clear_limit=clear_limit)
      File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2379, in wrapped_count
        return rq.scalar() or 0
      File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2135, in scalar
        row = self._execute().fetchone()
      File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2126, in _execute
        return self.database.execute_sql(sql, params, self.require_commit)
      File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2728, in execute_sql
        self.commit()
      File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2597, in __exit__
        reraise(new_type, new_type(*exc_value.args), traceback)
      File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2720, in execute_sql
        cursor.execute(sql, params or ())
      File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 132, in execute
        result = self._query(query)
      File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 271, in _query
        conn.query(q)
      File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 725, in query
        self._execute_command(COM_QUERY, sql)
      File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 888, in _execute_command
        self._write_bytes(prelude + sql[:chunk_size-1])
      File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 848, in _write_bytes
        raise OperationalError(2006, "MySQL server has gone away (%r)" % (e,))
    OperationalError: (2006, "MySQL server has gone away (error(32, 'Broken pipe'))")
    
    
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/peewee.py", line 3385, in get
        return sq.get()
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/peewee.py", line 2389, in get
        return clone.execute().next()
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/peewee.py", line 2430, in execute
        self._qr = ResultWrapper(model_class, self._execute(), query_meta)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/peewee.py", line 2126, in _execute
        return self.database.execute_sql(sql, params, self.require_commit)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/peewee.py", line 2728, in execute_sql
        self.commit()
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/peewee.py", line 2597, in __exit__
        reraise(new_type, new_type(*exc_value.args), traceback)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/peewee.py", line 2720, in execute_sql
        cursor.execute(sql, params or ())
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/cursors.py", line 132, in execute
        result = self._query(query)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/cursors.py", line 271, in _query
        conn.query(q)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/connections.py", line 726, in query
        self._affected_rows = self._read_query_result(unbuffered=unbuffered)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/connections.py", line 861, in _read_query_result
        result.read()
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/connections.py", line 1064, in read
        first_packet = self.connection._read_packet()
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/connections.py", line 825, in _read_packet
        packet = packet_type(self)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/connections.py", line 242, in __init__
        self._recv_packet(connection)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/connections.py", line 248, in _recv_packet
        packet_header = connection._read_bytes(4)
      File "/Users/max/code/content-service/venv-cs/lib/python2.7/site-packages/pymysql/connections.py", line 838, in _read_bytes
        2013, "Lost connection to MySQL server during query (%r)" % (e,))
    OperationalError: (2013, "Lost connection to MySQL server during query (timeout('timed out',))")
    
    opened by cpury 31
  • Proposal for plugins support

    Proposal for plugins support

    There is currently no easy way to build third party plugins within the same namespace as peewee, aside from monkeypatching.

    Projects such as pytest allow plugins to be registered into the Peewee namespace, giving a much cleaner syntax, as seen here, for example;

    from pytest_raisesregexp import raisesregexp # without plugins
    from pytest import raises_regexp # with plugins
    

    This is apparently achieved via entry_points as seen here, and allows the plugin to interact via hooks, as seen here. In particular, the plugin can access pytest_namespace which allows manipulation of the namespace.

    The use case in this situation would be for providing third party plugins which are not suitable for the core or playhouse, whilst keeping everything in a single namespace.

    from peewee_dbmanager import DBManager # without plugins
    from peewee.dbmanager import DBManager # with plugins OR
    from peewee import DBManager # with plugins
    

    This could also open the door for potentially cleaning up Playhouse, allowing each of the extensions to be segregated into it's own plugin, keeping the core repo clean and lean.

    Thoughts @coleifer ?

    opened by foxx 28
  • Allow to define database dynamically

    Allow to define database dynamically

    Peewee is awesome :+1: :)

    I'm using Peewee in my Tornado-powered project and I need to define database connection dynamically, like that:

    import tornado
    from peewee import *
    
    class Application(tornado.web.Application):
    
        def __init__(self, **kwargs):
            # Some init stuff ...
    
            # Setup DB and Models
    
            self.database = PostgresqlDatabase('mydb', user='postgres')
    
            import myapp.users.models as users
            self.User = self._get_model(users.User, self.database)
    
            # etc...
    
        def _get_model(self, model, db):
            model._meta.database = db
            model.create_table(True)
            return model
    

    So, I'm using private undocumented _meta property and I'm not feeling ok about that and I'm not sure what is the best design decision for that.

    Anybody have ideas?

    opened by rudyryk 26
  • ImproperlyConfigured: MySQLdb must be installed.

    ImproperlyConfigured: MySQLdb must be installed.

    I am getting this any time I am trying to create a table or do something with connected DB, althrough I installed MySQL-python lib and it looks working

    opened by Casyfill 24
  • get_or_create failing

    get_or_create failing

    Hello,

    So millions of times get_or_create works fine, but sometimes I am crashing on what is a legal select statement ! Using newest peewee ...

    As a test would like to try the get and create manually, but am unsure how to write that now.

    Here are some of what I have found. The table t1 has a primary key named "code". There is no "id" defined on the table. AND if I run this query select below in psql it works fine ... but for some reason peewee crashes with it ...

    Naturally I have a dictionary with the value {'code': 'LR091816'}
    How would I do a select manually in peewee passing in this dictionary, figure it would be a good test. I tried model.select().where(d) but that did not seem to work.

    Here is the log ...

    ('SELECT "t1"."code" FROM "game" AS "t1" WHERE ("t1"."code" = %s) LIMIT %s OFFSET %s', [u'LR091816', 1, 0]) * get create failed! Traceback (most recent call last): File "/home/anon/Desktop/test/precast_ingest_util.py", line 146, in create_or_increment model.get_or_create(**kwargs) # the select fails File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 5344, in get_or_create return query.get(), False File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 5705, in get return clone.execute()[0] File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1522, in inner return method(self, database, *args, **kwargs) File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1593, in execute return self._execute(database) File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1747, in _execute cursor = database.execute(self) File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2590, in execute return self.execute_sql(sql, params, commit=commit) File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2584, in execute_sql self.commit() File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2377, in exit reraise(new_type, new_type(*exc_args), traceback) File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2577, in execute_sql cursor.execute(sql, params or ()) InternalError: current transaction is aborted, commands ignored until end of transaction block

    current transaction is aborted, commands ignored until end of transaction block

    {'code': 'LR091816'}

    opened by ra-esmith 23
  • Allow asynchronous queries

    Allow asynchronous queries

    Hard one, as peewee has been built on top of synchronous DB driver up untill then, but with Python 3.4 comming and shipping with asyncio + yield from, this can be an interesting for Python 3 users.

    opened by sametmax 23
  • pwiz not generating foreign key fields for postgres

    pwiz not generating foreign key fields for postgres

    Hi @coleifer

    I have the same issue as https://github.com/coleifer/peewee/issues/207 with postgres however

    I used the command python -m pwiz -e postgresql -u USERNAME -P DATABASE -H HOST-p PORT-s SCHEMA> model.py

    My model did not contain any "ForeignKeyField"

    Any thoughts? Does pwiz create foreign key for postgres?

    opened by Mak-NOAA 22
  • peewee.InterfaceError: (0, '') | pymysql.err.InterfaceError: (0, '')

    peewee.InterfaceError: (0, '') | pymysql.err.InterfaceError: (0, '')

    I am encountering this exception (using peewee-async high-level api) then my asyncio based daemon is idle (about 15 minutes or so), after that time, this exception occurs, I have tried to close and reopen connection, and was surprised that Manager (high-level api) did not track its state (close/reopen is done using its methods).

    Traceback (most recent call last):
      File "/home/ubuntu/dati-server/match/manager.py", line 56, in clean_up_user
        await exam_end_all_handler(current_match_id, user_id)
      File "/home/ubuntu/dati-server/match/manager.py", line 407, in exam_end_all_handler
        match_result = await MatchUserRecord.set_user_score_and_winner(match_id)
      File "/home/ubuntu/dati-server/models/record.py", line 260, in set_user_score_and_winner
        score1 = await user_record1.get_match_score()
      File "/home/ubuntu/dati-server/models/record.py", line 108, in get_match_score
        answer_records = await objects.execute(UserAnswerRecord.filter(match_id=self.match.id, user_id=self.user_id))
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 1386, in __get__
        return self.get_object_or_id(instance)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 1377, in get_object_or_id
        obj = self.rel_model.get(self.field.to_field == rel_id)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 4988, in get
        return sq.get()
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3220, in get
        return next(clone.execute())
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3274, in execute
        self._qr = ResultWrapper(model_class, self._execute(), query_meta)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 2939, in _execute
        return self.database.execute_sql(sql, params, self.require_commit)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee_async.py", line 1041, in execute_sql
        return super().execute_sql(*args, **kwargs)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3837, in execute_sql
        self.commit()
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3656, in __exit__
        reraise(new_type, new_type(*exc_args), traceback)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 135, in reraise
        raise value.with_traceback(tb)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3830, in execute_sql
        cursor.execute(sql, params or ())
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/pymysql/cursors.py", line 165, in execute
        result = self._query(query)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
        conn.query(q)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/pymysql/connections.py", line 859, in query
        self._execute_command(COMMAND.COM_QUERY, sql)
      File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in _execute_command
        raise err.InterfaceError("(0, '')")
    peewee.InterfaceError: (0, '')
    
    opened by MozzieHan 21
  • I want to change order of table creation for create_tables()

    I want to change order of table creation for create_tables()

    Basically, I need to make Peewee know that some of my tables are inherited from other tables, and this means that the «parents» should be created before «children».

    Can you please move some logic out from sort_models_topologically(), so that I could override it in my classes and customize list of a table's dependencies (which now includes only those tables to which it has foreign keys referencing)? Or, could you make support for inheritance in Peewee natively?

    opened by maaaks 21
Releases(3.15.4)
  • 3.15.4(Nov 11, 2022)

    • Raise an exception in ReconnectMixin if connection is lost while inside a transaction (if the transaction was interrupted presumably some changes were lost and explicit intervention is needed).
    • Add db.Model property to reduce boilerplate.
    • Add support for running prefetch() queries with joins instead of subqueries (this helps overcome a MySQL limitation about applying LIMITs to a subquery).
    • Add SQL AVG to whitelist to avoid coercing by default.
    • Allow arbitrary keywords in metaclass constructor, #2627
    • Add a pyproject.toml to silence warnings from newer pips when wheel package is not available.

    This release has a small helper for reducing boilerplate in some cases by exposing a base model class as an attribute of the database instance.

    # old:
    db = SqliteDatabase('...')
    
    class BaseModel(Model):
        class Meta:
            database = db
    
    class MyModel(BaseModel):
        pass
    
    # new:
    db = SqliteDatabase('...')
    
    class MyModel(db.Model):
        pass
    

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.15.3(Sep 22, 2022)

    • Add scalars() query method (complements scalar()), roughly equivalent to writing [t[0] for t in query.tuples()].
    • Small doc improvements
    • Fix and remove some flaky test assertions with Sqlite INSERT + RETURNING.
    • Fix innocuous failing Sqlite test on big-endian machines.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.15.2(Sep 22, 2022)

    • Fix bug where field-specific conversions were being applied to the pattern used for LIKE / ILIKE operations. Refs #2609
    • Fix possible infinite loop when accidentally invoking the __iter__ method on certain Column subclasses. Refs #2606
    • Add new helper for specifying which Model a particular selected column-like should be bound to, in queries with joins that select from multiple sources.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.15.1(Jul 12, 2022)

    • Fix issue introduced in Sqlite 3.39.0 regarding the propagation of column subtypes in subqueries. Affected sqlite changelog extension when used with JSON columns.
    • Fix bug where cockroachdb server version was not set when beginning a transaction on an unopened database.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.15.0(Jun 17, 2022)

    Rollback behavior change in commit ab43376697 (GH #2026). Peewee will no longer automatically return the cursor rowcount for certain bulk-inserts. This should only affect users of MySQL and Sqlite who relied on a bulk INSERT returning the rowcount (as opposed to the cursor's lastrowid). The rowcount behavior is still available chaining the as_rowcount() method:

    # NOTE: this change only affects MySQL or Sqlite.
    db = MySQLDatabase(...)
    
    # Previously, bulk inserts of the following forms would return the rowcount.
    query = User.insert_many(...)  # Bulk insert.
    query = User.insert_from(...)  # Bulk insert (INSERT INTO .. SELECT FROM).
    
    # Previous behavior (peewee 3.12 - 3.14.10):
    # rows_inserted = query.execute()
    
    # New behavior:
    last_id = query.execute()
    
    # To get the old behavior back:
    rows_inserted = query.as_rowcount().execute()
    

    This release contains a fix for a long-standing request to allow data-modifying queries to support CTEs. CTEs are now supported for use with INSERT, DELETE and UPDATE queries - see #2152.

    Additionally, this release adds better support for using the new RETURNING syntax with Sqlite automatically. Specify returing_clause=True when initializing your SqliteDatabase and all bulk inserts will automatically specify a RETURNING clause, returning the newly-inserted primary keys. This functionality requires Sqlite 3.35 or newer.

    Smaller changes:

    • Add shortcuts.insert_where() helper for generating conditional INSERT with a bit less boilerplate.
    • Fix bug in test_utils.count_queres() which could erroneously include pool events such as connect/disconnect, etc.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.10(Mar 7, 2022)

    • Add shortcut for conditional insert using sub-select, see #2528
    • Add convenience left_outer_join() method to query.
    • Add selected_columns property to Select queries.
    • Add name property to Alias instances.
    • Fix regression in tests introduced by change to DataSet in 3.14.9.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.9(Feb 22, 2022)

    • Allow calling table_exists() with a model-class, refs
    • Improve is_connection_usable() method of MySQLDatabase class.
    • Better support for VIEWs with playhouse.dataset.DataSet and sqlite-web.
    • Support INSERT / ON CONFLICT in playhosue.kv for newer Sqlite.
    • Add ArrayField.contained_by() method, a corollary to contains() and the contains_any() methods.
    • Support cyclical foreign-key relationships in reflection/introspection, and also for sqlite-web.
    • Add magic methods for FTS5 field to optimize, rebuild and integrity check the full-text index.
    • Add fallbacks in setup.py in the event distutils is not available.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.8(Oct 28, 2021)

    Back-out all changes to automatically use RETURNING for SqliteExtDatabase, CSqliteExtDatabase and APSWDatabase. The issue I found is that when a RETURNING cursor is not fully-consumed, any parent SAVEPOINT (and possibly transaction) would not be able to be released. Since this is a backwards-incompatible change, I am going to back it out for now.

    Returning clause can still be specified for Sqlite, however it just needs to be done so manually rather than having it applied automatically.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.7(Oct 27, 2021)

  • 3.14.6(Oct 27, 2021)

  • 3.14.5(Oct 27, 2021)

    Note: this release contained a bug and has been removed from PyPI. Use 3.14.6 or newer.

    This release contains a number of bug-fixes and small improvements.

    • Only raise DoesNotExist when lazy_load is enabled on ForeignKeyField, fixes issue #2377.
    • Add missing convenience method ModelSelect.get_or_none()
    • Allow ForeignKeyField to specify a custom BackrefAccessorClass, references issue #2391.
    • Ensure foreign-key-specific conversions are applied on INSERT and UPDATE, fixes #2408.
    • Add handling of MySQL error 4031 (inactivity timeout) to the ReconnectMixin helper class. Fixes #2419.
    • Support specification of conflict target for ON CONFLICT/DO NOTHING.
    • Add encoding parameter to the DataSet freeze() and thaw() methods, fixes #2425.
    • Fix bug which prevented DeferredForeignKey from being used as a model's primary key, fixes #2427.
    • Ensure foreign key's related object cache is cleared when the foreign-key is set to None. Fixes #2428.
    • Allow specification of (schema, table) to be used with CREATE TABLE AS..., fixes #2423.
    • Allow reusing open connections with DataSet, refs #2441.
    • Add highlight() and snippet() helpers to Sqlite SearchField, for use with full-text search extension.
    • Preserve user-provided aliases in column names. Fixes #2453.
    • Add support for Sqlite 3.37 strict tables.
    • Ensure database is inherited when using ThreadSafeDatabaseMetadata, and also adds an implementation in playhouse.shortcuts along with basic unit tests.
    • Better handling of Model's dirty fields when saving, fixes #2466.
    • Add basic support for MariaDB connector driver in playhouse.mysql_ext, refs issue #2471.
    • Begin a basic implementation for a psycopg3-compatible pg database, refs issue #2473.
    • Add provisional support for RETURNING when using the appropriate versions of Sqlite or MariaDB.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.4(Mar 19, 2021)

    This release contains an important fix for a regression introduced by commit ebe3ad5, which affected the way model instances are converted to parameters for use in expressions within a query. The bug could manifest when code uses model instances as parameters in expressions against fields that are not foreign-keys.

    The issue is described in #2376.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.3(Mar 11, 2021)

    This release contains a single fix for ensuring NULL values are inserted when issuing a bulk-insert of heterogeneous dictionaries which may be missing explicit NULL values. Fixes issue #2638.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.2(Mar 4, 2021)

    This is a small release mainly to get some fixes out.

    • Support for named Check and foreign-key constraints.
    • Better foreign-key introspection for CockroachDB (and Postgres).
    • Register UUID adapter for Postgres.
    • Add fn.array_agg() to blacklist for automatic value coercion.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.1(Feb 7, 2021)

    This release contains primarily bugfixes.

    • Properly delegate to a foreign-key field's db_value() function when converting model instances. #2304.
    • Strip quote marks and parentheses from column names returned by sqlite cursor when a function-call is projected without an alias. #2305.
    • Fix DataSet.create_index() method, #2319.
    • Fix column-to-model mapping in model-select from subquery with joins, #2320.
    • Improvements to foreign-key lazy-loading thanks @conqp, #2328.
    • Preserve and handle CHECK() constraints in Sqlite migrator, #2343.
    • Add stddev aggregate function to collection of sqlite user-defined funcs.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.14.0(Nov 7, 2020)

    This release has been a bit overdue and there are numerous small improvements and bug-fixes. The bugfix that prompted this release is #2293, which is a regression in the Django-inspired .filter() APIs that could cause some filter expressions to be discarded from the generated SQL. Many thanks for the excellent bug report, Jakub.

    • Add an experimental helper, shortcuts.resolve_multimodel_query(), for resolving multiple models used in a compound select query.
    • Add a lateral() method to select query for use with lateral joins, refs issue #2205.
    • Added support for nested transactions (savepoints) in cockroach-db (requires 20.1 or newer).
    • Automatically escape wildcards passed to string-matching methods, refs #2224.
    • Allow index-type to be specified on MySQL, refs #2242.
    • Added a new API, converter() to be used for specifying a function to use to convert a row-value pulled off the cursor, refs #2248.
    • Add set() and clear() method to the bitfield flag descriptor, refs #2257.
    • Add support for range types with IN and other expressions.
    • Support CTEs bound to compound select queries, refs #2289.

    Bug-fixes

    • Fix to return related object id when accessing via the object-id descriptor, when the related object is not populated, refs #2162.
    • Fix to ensure we do not insert a NULL value for a primary key.
    • Fix to conditionally set the field/column on an added column in a migration, refs #2171.
    • Apply field conversion logic to model-class values. Relocates the logic from issue #2131 and fixes #2185.
    • Clone node before modifying it to be flat in an enclosed nodelist expr, fixes issue #2200.
    • Fix an invalid item assignment in nodelist, refs #2220.
    • Fix an incorrect truthiness check used with save() and only=, refs #2269.
    • Fix regression in filter() where using both *args and **kwargs caused the expressions passed as args to be discarded. See #2293.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.13.3(Apr 24, 2020)

    • Allow arbitrary keyword arguments to be passed to DataSet constructor, which are then passed to the instrospector.
    • Allow scalar subqueries to be compared using numeric operands.
    • Fix bulk_create() when model being inserted uses FK identifiers.
    • Fix bulk_update() so that PK values are properly coerced to the right data-type (e.g. UUIDs to strings for Sqlite).
    • Allow array indices to be used as dict keys, e.g. for the purposes of updating a single array index value.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.13.2(Mar 27, 2020)

    • Allow aggregate functions to support an ORDER BY clause, via the addition of an order_by() method to the function (fn) instance. Refs #2094.
    • Fix prefetch() bug, where related "backref" instances were marked as dirty, even though they had no changes. Fixes #2091.
    • Support LIMIT 0. Previously a limit of 0 would be translated into effectively an unlimited query on MySQL. References #2084.
    • Support indexing into arrays using expressions with Postgres array fields. References #2085.
    • Ensure postgres introspection methods return the columns for multi-column indexes in the correct order. Fixes #2104.
    • Add support for arrays of UUIDs to postgres introspection.
    • Fix introspection of columns w/capitalized table names in postgres (#2110).
    • Fix to ensure correct exception is raised in SqliteQueueDatabase when iterating over cursor/result-set.
    • Fix bug comparing subquery against a scalar value. Fixes #2118.
    • Fix issue resolving composite primary-keys that include foreign-keys when building the model-graph. Fixes #2115.
    • Allow model-classes to be passed as arguments, e.g., to a table function. Refs #2131.
    • Ensure postgres JSONField.concat() accepts expressions as arguments.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.13.1(Dec 6, 2019)

    Fix a regression when specifying keyword arguments to the atomic() or transaction() helper methods. Note: this only occurs if you were using Sqlite and were explicitly setting the lock_type= parameter.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.13.0(Dec 6, 2019)

    CockroachDB support added

    This will be a notable release as it adds support for CockroachDB, a distributed, horizontally-scalable SQL database.

    Other features and fixes

    • Allow FOR UPDATE clause to specify one or more tables (FOR UPDATE OF...).
    • Support for Postgres LATERAL join.
    • Properly wrap exceptions raised during explicit commit/rollback in the appropriate peewee-specific exception class.
    • Capture original exception object and expose it as exc.orig on the wrapped exception.
    • Properly introspect SMALLINT columns in Postgres schema reflection.
    • More flexible handling of passing database-specific arguments to atomic() and transaction() context-manager/decorator.
    • Fix non-deterministic join ordering issue when using the filter() API across several tables (#2063).

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.12.0(Nov 24, 2019)

    • Bulk insert (insert_many() and insert_from()) will now return the row count instead of the last insert ID. If you are using Postgres, peewee will continue to return a cursor that provides an iterator over the newly-inserted primary-key values by default. This behavior is being retained by default for compatibility. Postgres users can simply specify an empty returning() call to disable the cursor and retrieve the rowcount instead.
    • Migration extension now supports altering a column's data-type, via the new alter_column_type() method.
    • Added Database.is_connection_usable() method, which attempts to look at the status of the underlying DB-API connection to determine whether the connection is usable.
    • Common table expressions include a materialized parameter, which can be used to control Postgres' optimization fencing around CTEs.
    • Added BloomFilter.from_buffer() method for populating a bloom-filter from the output of a previous call to the to_buffer() method.
    • Fixed APSW extension's commit() and rollback() methods to no-op if the database is in auto-commit mode.
    • Added generate_always= option to the IdentityField (defaults to False).

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.11.2(Sep 24, 2019)

  • 3.11.1(Sep 23, 2019)

  • 3.11.0(Sep 19, 2019)

    • Fixes #1991. This particular issue involves joining 3 models together in a chain, where the outer two models are empty. Previously peewee would make the middle model an empty model instance (since a link might be needed from the source model to the outermost model). But since both were empty, it is more correct to make the intervening model a NULL value on the foreign-key field rather than an empty instance.
    • An unrelated fix came out of the work on #1991 where hashing a model whose primary-key happened to be a foreign-key could trigger the FK resolution query. This patch fixes the Model._pk and get_id() interfaces so they no longer introduce the possibility of accidentally resolving the FK.
    • Allow Field.contains(), startswith() and endswith() to compare against another column-like object or expression.
    • Workaround for MySQL prior to 8 and MariaDB handling of union queries inside of parenthesized expressions (like IN).
    • Be more permissive in letting invalid values be stored in a field whose type is INTEGER or REAL, since Sqlite allows this.
    • TimestampField resolution cleanup. Now values 0 and 1 will resolve to a timestamp resolution of 1 second. Values 2-6 specify the number of decimal places (hundredths to microsecond), or alternatively the resolution can still be provided as a power of 10, e.g. 10, 1000 (millisecond), 1e6 (microsecond).
    • When self-referential foreign-keys are inherited, the foreign-key on the subclass will also be self-referential (rather than pointing to the parent model).
    • Add TSV import/export option to the dataset extension.
    • Add item interface to the dataset.Table class for doing primary-key lookup, assignment, or deletion.
    • Extend the mysql ReconnectMixin helper to work with mysql-connector.
    • Fix mapping of double-precision float in postgres schema reflection. Previously it mapped to single-precision, now it correctly uses a double.
    • Fix issue where PostgresqlExtDatabase and MySQLConnectorDatabase did not respect the autoconnect setting.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.10.0(Aug 3, 2019)

    • Add a helper to playhouse.mysql_ext for creating Match full-text search expressions.
    • Added date-part properties to TimestampField for accessing the year, month, day, etc., within a SQL expression.
    • Added to_timestamp() helper for DateField and DateTimeField that produces an expression returning a unix timestamp.
    • Add autoconnect parameter to Database classes. This parameter defaults to True and is compatible with previous versions of Peewee, in which executing a query on a closed database would open a connection automatically. To make it easier to catch inconsistent use of the database connection, this behavior can now be disabled by specifying autoconnect=False, making an explicit call to Database.connect() needed before executing a query.
    • Added database-agnostic interface for obtaining a random value.
    • Allow isolation_level to be specified when initializing a Postgres db.
    • Allow hybrid properties to be used on model aliases. Refs #1969.
    • Support aggregates with FILTER predicates on the latest Sqlite.

    Changes

    • More aggressively slot row values into the appropriate field when building objects from the database cursor (rather than using whatever cursor.description tells us, which is buggy in older Sqlite).
    • Be more permissive in what we accept in the insert_many() and insert() methods.
    • When implicitly joining a model with multiple foreign-keys, choose the foreign-key whose name matches that of the related model. Previously, this would have raised a ValueError stating that multiple FKs existed.
    • Improved date truncation logic for Sqlite and MySQL to make more compatible with Postgres' date_trunc() behavior. Previously, truncating a datetime to month resolution would return '2019-08' for example. As of 3.10.0, the Sqlite and MySQL date_trunc implementation returns a full datetime, e.g. '2019-08-01 00:00:00'.
    • Apply slightly different logic for casting JSON values with Postgres. Previously, Peewee just wrapped the value in the psycopg2 Json() helper. In this version, Peewee now dumps the json to a string and applies an explicit cast to the underlying JSON data-type (e.g. json or jsonb).

    Bug fixes

    • Save hooks can now be called for models without a primary key.
    • Fixed bug in the conversion of Python values to JSON when using Postgres.
    • Fix for differentiating empty values from NULL values in model_to_dict.
    • Fixed a bug referencing primary-key values that required some kind of conversion (e.g., a UUID). See #1979 for details.
    • Add small jitter to the pool connection timestamp to avoid issues when multiple connections are checked-out at the same exact time.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.9.6(Jun 3, 2019)

    • Support nesting the Database instance as a context-manager. The outermost block will handle opening and closing the connection along with wrapping everything in a transaction. Nested blocks will use savepoints.
    • Add new session_start(), session_commit() and session_rollback() interfaces to the Database object to support using transactional controls in situations where a context-manager or decorator is awkward.
    • Fix error that would arise when attempting to do an empty bulk-insert.
    • Set isolation_level=None in SQLite connection constructor rather than afterwards using the setter.
    • Add create_table() method to Select query to implement CREATE TABLE AS.
    • Cleanup some declarations in the Sqlite C extension.
    • Add new example showing how to implement Reddit's ranking algorithm in SQL.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.9.5(Apr 26, 2019)

    • Added small helper for setting timezone when using Postgres.
    • Improved SQL generation for VALUES clause.
    • Support passing resolution to TimestampField as a power-of-10.
    • Small improvements to INSERT queries when the primary-key is not an auto-incrementing integer, but is generated by the database server (eg uuid).
    • Cleanups to virtual table implementation and python-to-sqlite value conversions.
    • Fixed bug related to binding previously-unbound models to a database using a context manager, #1913.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.9.4(Apr 14, 2019)

    • Add Model.bulk_update() method for bulk-updating fields across multiple model instances. Docs.
    • Add lazy_load parameter to ForeignKeyField. When initialized with lazy_load=False, the foreign-key will not use an additional query to resolve the related model instance. Instead, if the related model instance is not available, the underlying FK column value is returned (behaving like the "_id" descriptor).
    • Added Model.truncate_table() method.
    • The reflection and pwiz extensions now attempt to be smarter about converting database table and column names into snake-case. To disable this, you can set snake_case=False when calling the Introspector.introspect() method or use the -L (legacy naming) option with the pwiz script.
    • Bulk insert via insert_many() no longer require specification of the fields argument when the inserted rows are lists/tuples. In that case, the fields will be inferred to be all model fields except any auto-increment id.
    • Add DatabaseProxy, which implements several of the Database class context managers. This allows you to reference some of the special features of the database object without directly needing to initialize the proxy first.
    • Add support for window function frame exclusion and added built-in support for the GROUPS frame type.
    • Add support for chaining window functions by extending a previously-declared window function.
    • Playhouse Postgresql extension TSVectorField.match() method supports an additional argument plain, which can be used to control the parsing of the TS query.
    • Added very minimal JSONField to the playhouse MySQL extension.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.9.3(Mar 23, 2019)

    • Added cross-database support for NULLS FIRST/LAST when specifying the ordering for a query. Previously this was only supported for Postgres. Peewee will now generate an equivalent CASE statement for Sqlite and MySQL.
    • Added EXCLUDED helper for referring to the EXCLUDED namespace used with INSERT...ON CONFLICT queries, when referencing values in the conflicting row data.
    • Added helper method to the model Metadata class for setting the table name at run-time. Setting the Model._meta.table_name directly may have appeared to work in some situations, but could lead to subtle bugs. The new API is Model._meta.set_table_name().
    • Enhanced helpers for working with Peewee interactively, see doc.
    • Fix cache invalidation bug in DataSet that was originally reported on the sqlite-web project.
    • New example script implementing a hexastore.

    View commits

    Source code(tar.gz)
    Source code(zip)
  • 3.9.2(Mar 6, 2019)

A wrapper for SQLite and MySQL, Most of the queries wrapped into commands for ease.

Before you proceed, make sure you know Some real SQL, before looking at the code, otherwise you probably won't understand anything. Installation pip i

Refined 4 Jul 30, 2022
A simple Python tool to transfer data from MySQL to SQLite 3.

MySQL to SQLite3 A simple Python tool to transfer data from MySQL to SQLite 3. This is the long overdue complimentary tool to my SQLite3 to MySQL. It

Klemen Tusar 126 Jan 3, 2023
Pandas on AWS - Easy integration with Athena, Glue, Redshift, Timestream, QuickSight, Chime, CloudWatchLogs, DynamoDB, EMR, SecretManager, PostgreSQL, MySQL, SQLServer and S3 (Parquet, CSV, JSON and EXCEL).

AWS Data Wrangler Pandas on AWS Easy integration with Athena, Glue, Redshift, Timestream, QuickSight, Chime, CloudWatchLogs, DynamoDB, EMR, SecretMana

Amazon Web Services - Labs 3.3k Dec 31, 2022
Asynchronous interface for peewee ORM powered by asyncio

peewee-async Asynchronous interface for peewee ORM powered by asyncio. Important notes Since version 0.6.0a only peewee 3.5+ is supported If you still

05Bit 666 Dec 30, 2022
Familiar asyncio ORM for python, built with relations in mind

Tortoise ORM Introduction Tortoise ORM is an easy-to-use asyncio ORM (Object Relational Mapper) inspired by Django. Tortoise ORM was build with relati

Tortoise 3.3k Dec 31, 2022
Async ORM based on PyPika

PyPika-ORM - ORM for PyPika SQL Query Builder The package gives you ORM for PyPika with asycio support for a range of databases (SQLite, PostgreSQL, M

Kirill Klenov 7 Jun 4, 2022
Create a database, insert data and easily select it with Sqlite

sqliteBasics create a database, insert data and easily select it with Sqlite Watch on YouTube a step by step tutorial explaining this code: https://yo

Mariya 27 Dec 27, 2022
A supercharged SQLite library for Python

SuperSQLite: a supercharged SQLite library for Python A feature-packed Python package and for utilizing SQLite in Python by Plasticity. It is intended

Plasticity 703 Dec 30, 2022
A tutorial designed to introduce you to SQlite 3 database using python

SQLite3-python-tutorial A tutorial designed to introduce you to SQlite 3 database using python What is SQLite? SQLite is an in-process library that im

null 0 Dec 28, 2021
A tool to snapshot sqlite databases you don't own

The core here is my first attempt at a solution of this, combining ideas from browser_history.py and karlicoss/HPI/sqlite.py to create a library/CLI tool to (as safely as possible) copy databases which may be in use from other applications

Sean Breckenridge 10 Dec 22, 2022
A library for python made by me,to make the use of MySQL easier and more pythonic

my_ezql A library for python made by me,to make the use of MySQL easier and more pythonic This library was made by Tony Hasson , a 25 year old student

null 3 Nov 19, 2021
PostgreSQL database access simplified

Queries: PostgreSQL Simplified Queries is a BSD licensed opinionated wrapper of the psycopg2 library for interacting with PostgreSQL. The popular psyc

Gavin M. Roy 251 Oct 25, 2022
A fast PostgreSQL Database Client Library for Python/asyncio.

asyncpg -- A fast PostgreSQL Database Client Library for Python/asyncio asyncpg is a database interface library designed specifically for PostgreSQL a

magicstack 5.8k Dec 31, 2022
aiopg is a library for accessing a PostgreSQL database from the asyncio

aiopg aiopg is a library for accessing a PostgreSQL database from the asyncio (PEP-3156/tulip) framework. It wraps asynchronous features of the Psycop

aio-libs 1.3k Jan 3, 2023
PostgreSQL database adapter for the Python programming language

psycopg2 - Python-PostgreSQL Database Adapter Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Its main fe

The Psycopg Team 2.8k Jan 5, 2023
Pure-python PostgreSQL driver

pg-purepy pg-purepy is a pure-Python PostgreSQL wrapper based on the anyio library. A lot of this library was inspired by the pg8000 library. Credits

Lura Skye 11 May 23, 2022
A Python wheel containing PostgreSQL

postgresql-wheel A Python wheel for Linux containing a complete, self-contained, locally installable PostgreSQL database server. All servers run as th

Michel Pelletier 71 Nov 9, 2022
Application which allows you to make PostgreSQL databases with Python

Automate PostgreSQL Databases with Python Application which allows you to make PostgreSQL databases with Python I used the psycopg2 library which is u

Marc-Alistair Coffi 0 Dec 31, 2021
Python PostgreSQL adapter to stream results of multi-statement queries without a server-side cursor

streampq Stream results of multi-statement PostgreSQL queries from Python without server-side cursors. Has benefits over some other Python PostgreSQL

Department for International Trade 6 Oct 31, 2022