coleifer / peewee Goto Github PK
View Code? Open in Web Editor NEWa small, expressive orm -- supports postgresql, mysql, sqlite and cockroachdb
Home Page: http://docs.peewee-orm.com/
License: MIT License
a small, expressive orm -- supports postgresql, mysql, sqlite and cockroachdb
Home Page: http://docs.peewee-orm.com/
License: MIT License
Floats, by definition, should only take 4 bytes.
Currently:
Proposal:
Hi, when initializing (create) record; sometimes; mostly we have to leave other non indexed fields with default value or null for future fill.
May I suggest you to allow null by default, it's rare of important field to be not null
class Field(object):
def __init__(self, null=True, db_index=False, unique=False, verbose_name=None,
help_text=None, *args, **kwargs):
null=True
Thanks
Table created in MySQL by create_table method:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `test_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Key on 'id' column is duplicated (PRIMARY KEY and UNIQUE KEY).
If I do something like this:
class User(peewee.Model):
email = peewee.CharField()
class Meta:
db_table = 'users'
That doesn't get picked up. It's because none such extra arguments are actually used when creating the _meta
attribute. See here: https://github.com/coleifer/peewee/blob/master/peewee.py#L1098
Hi, to speed-up my query tasks, by default I have turned-off autocommit to False in db configuration,
means we need to call db.commit() to commit the transaction
Found now behavior doesn't flush (save) dirty objects on commit.
Again, in sql-alchemy we don't need explicit call object.save() as we know there's dirty objects that need to be save() automatically on commit.
try:
othermodel.create(blablafield = 'blabla value')
mymodel = MyModel.select().first() # hehe new stuff :D
mymodel.somefield = 'edit the field value' # dirty object
# mymodel.save() # maybe explicitly required only on autocommit scheme
db.commit()
except :
db.rollback()
I'm playing with more than 4 models in one task, which changing the field value anywhere,
it's a bit difficult to catch which object should call save() on the end of procedure block since changes could be applied on several place of code block logic
Currently you can only define IntegerField, FloatField and DecimalField.
What happens if you need to store a long (bigint)?
You have to use a Decimal without decimal places?
A new LongField (or BigIntField) should be added (bigint, 8 bytes).
Also, sometimes float is not enough.
For coherence, DoubleField should also be added (double precision, 8 bytes).
I believe both cases are extremely frequent.
Would be nice to be able to populate (or partially populate) instances of related objects when querying. Example would be querying a list of blog entries and wanting to additionally select the 'name' of the blog -- this can be done in 1 query with a join.
It shouldn't be too hard... related models can be queried passing a dictionary to Model.select() -- need to add the logic to the QueryResultWrapper. Looking at the returned rows and converting them into a dictionary may not work here if column names collide, so might need to list the columns and assign based on index.
Now that fields are exposed using descriptors it sohuld be possible to do "deferred" loading of field data as well, i.e. if it wasn't selected but then is asked for.
I don't know Django very well, but as far as I could tell, Django doesn't use the Meta class in models to select the database to use, and probably for a good reason?
I really like peewee, but I had to restructure some of my base application flow because all Peewee model classes have to have the database instance available immediately on class creation and in some global scope. The examples of course look fine, but for an application like mine that is larger and uses blueprints and all that, the Meta/inheritance system was a bit awkward to use.
do you think there's a way to just have a global bind variable? Like Elixir:
elixir.metadata.bind = "sqlite:///db.sqlite"
Put simply, it would be nice to have a way to set the database object after the Model classes have been created.
I have an SQLite database which has a DateTimeField() with a few different datetime formats (not on purpose). I will probably fix my datetime values, but I thought I'd post this in case you feel peewee should handle this gracefully?
2011-11-15 21:53:59.759451+0000
2011-11-28 20:12:03
2009-01-01 (error)
http://paste.pocoo.org/show/aH7tITRfn9mhczxgWq3u/
Thanks for Peewee (and Flask-Peewee)!
Although there are no automated 'migrations' in peewee, it would be helpful to give users a way to get column sql and generate alter table queries (or with sqlite, since there's no drop column, maybe the temptable thing).
Hello Charles,
want to use 'select' in the following way.
<< User.select(['id', 'username', R('LOWER(SUBSTR( %s, 1, 1))', 'myFirstName' , 'first_letter')]) >>
but there is no implementation using values and alias together ?
Have you any idea how to fix it?
Kind regards,
Chris
Would like a way to do self-referential foreign keys.
There seems to be no way to declare a foreign_key which does not end with "_id", since the expected column name is attribute_name + '_id'. Or did I miss way to declare such a key?
The docs say that MyModel.create_table
will noop if the table already exists, but currently it raises a sqlite3.OperationalError
instead.
It looks like the Database class method is ready for the safe
version, but there is no way to use that from models.
It'd be nice if you could pass the safe
boolean to MyModel.create_table
like you can to MyModel.drop_table
With the following I can access 'min' and 'max', but not 'price1'.
products = Product.select({
Product: ['*'],
Item: ['price1', Min('price1'), Max('price1')],
}).where(category_id=id).group_by('id').join(Item)
SQL:
('SELECT t1."id", t1."brand", t1."category_id", t1."name", t1."overview", t1."details", t2."price1", MIN(t2."price1") AS min, MAX(t2."price1") AS max FROM "products" AS t1 INNER JOIN "items" AS t2 ON t1."id" = t2."product_id" WHERE t1."category_id" = ? GROUP BY t1."id" ORDER BY t1."brand" ASC, t1."name" ASC', [u'29'])
When you create a model that has default field values they get used when persisting to the db (and subsequently when being initialized from the db) but not when a new object is instantiated. A contrived example:
class Post(peewee.Model):
comment_count = peewee.IntegerField(default=0)
post = Post()
post.comment_count # => None
post.save()
post.comment_count # => 0
I think it'd be more consistent if the defaults were applied to all objects of the class, not just ones that have been persisted or come from the database.
The change is quite simple, I believe. We just need to modify the Model
class's __init__
method like so:
def __init__(self, *args, **kwargs):
self.get_field_dict()
for k, v in kwargs.items():
setattr(self, k, v)
If this is something you'd be willing to merge, I'll happily do the work and submit a pull request.
Let me know, thanks.
get() is not suitable in some cases, for example if we need get first row from complex select-where query, using join, sub-query etc (without exception)
Can you please add a first() or using get() name too method to QueryResultWrapper class?
The method should return None on empty resultset, otherwise return first row
Please check of mind:
class QueryResultWrapper(object):
# ...
#def get(self): or which suitable
def first(self):
if self._result_cache:
return self._result_cache[0]
else:
row = self.cursor.fetchone()
if row:
row_dict = self._row_to_dict(row, self.cursor)
instance = self.model_from_rowset(self.model, row_dict)
self._result_cache.append(instance)
return instance
else:
self._populated = True
return None
In [6]: sq.sql_meta()
Out[6]:
('SELECT t2."feature", SUM(t1."count") AS count FROM "featurecount" AS t1 INNER JOIN "feature" AS t2 ON t1."feature_id" = t2."id" GROUP BY t2."feature"',
[],
{'columns': [(models.FeatureCount, ('SUM', 'count', 'count')),
(models.Feature, 'feature')],
'graph': {models.FeatureCount: [(models.Feature, None, None)]}})
Should be:
'columns': [(models.Feature, 'feature'), (models.FeatureCount, ('SUM', 'count', 'count'))]
It is not possible to create an "OR" query comparing columns from two tables. This is due to the way where clauses are created programmatically:
# where clauses between models are separated by calls to join() or switch()
Blog.select().where(Q( ??? )).join(Entry).where( Q(???) )
With the new .filter() syntax, it is syntactically possible to create these types of queries:
Blog.filter(Q(title='my blog') | Q(entry__title='some special entry'))
But there is no underlying mechanism to generate the right SQL since where clauses are stored in a dictionary keyed by the model they pertain to.
Fixing this will most likely require significant rewriting.
A simple example:
Model class
class Entity(Model):
num = IntegerField(default=0)
peewee seems no way for sql:
update Entity set num=num+1 where num>?
Hi.
I use peewee 0.8.0 and MySQL5.5.14,SQLite3.7.5 on MacOSX Lion.
Is table name not allowed like MyTable1 and My1Table?
Numerical value is replaced '_' automatically.
e.g.
MyTable1.create_table() ---> mytable_
My1Table.create_table() ---> my_table
Is this specifications?
Thank you.
The orm generates this sql string
CREATE TABLE thethable (stat INTEGER NOT NULL, group VARCHAR(255) NOT NULL)
for a model that has stat an integerField and group a CharField.
sqlite3 then gives an operationalError: near "group" syntax error
probably since group is a keyword, but if we put quote marks around group, the statement passes.
a possible fix is to change the line 1803 (Field class, to_sql method) in peewee.py from return '%s %s' % (self.name, rendered) to
return '"%s" %s' % (self.name, rendered)
Problems started after Commit: 62bd00b
Whole speak is about MySQL as backend
inside one of models I have method:
def getAbbrByCountryId(self, country_id):
return CC_map.select(
{CC_map: ['id AS cc_map_id, currency_id'],
Currencies: ['currency_abbr']}
).order_by('country_id').where(country_id=country_id
).join(Currencies, on='currency_id')
When you provided "smart select_related() behavior" I have no access to attributes created in code above.
Tried latest version and got problems with not assigning table link name to column names.
I get:
SELECT id AS cc_map_id, currency_id, t2.
currency_abbr
FROM cc_map
AS t1
INNER JOIN currencies
AS t2 ON t1.currency_id
= t2.id
WHERE t1.country_id
=1
ORDER BY t1.country_id
ASC
instead of
SELECT t1.id AS cc_map_id, t1.currency_id, t2.
currency_abbr
FROM cc_map
AS t1
INNER JOIN currencies
AS t2 ON t1.currency_id
= t2.id
WHERE t1.country_id
=1
ORDER BY t1.country_id
ASC
Tell If you need any additional info - I'll provide it.
P.S.
Thanks for your great work!
FROM @23doors:
There doesn't seem to be any kind of for update functionality? This is really very important for simple row-lock operations.
What I mean is a functionality like this one:
http://docs.sqlalchemy.org/en/latest/core/expression_api.html#sqlalchemy.sql.expression.select
(for_update parameter)
I may have missed this in the docs, but does Peewee support in memory SQLite databases? Thanks
Hi,
It would be nice if something like that could work
import peewee
Class CoreModel(peewee.Model):
id = peewee.PrimaryKeyField()
created = peewee.DatetimeField()
Class Meta:
database = db
Class User(CoreModel):
firstname = peewee.CharField()
Class Car(CoreModel):
brand = peewee.CharField()
I don't want to duplicate my fields.
Also for the id when I'm using a global ID.
As of 0.3.1, peewee requires both psycopg2 and sqlite3, even if you only intend to use one of them.
I don't know the name of my database until runtime. Is there any way to support this?
Thanks
i want to save image in database by peewee.
but peewee has not BlobField yet.
At least under MySQL, DecimalField ignores the precision parameters passed to the constructor, and the created table always carries the decimal field with precision 10,5.
Example code:
class Foo(peewee.Model):
foo_field = DecimalField(15,2)
Foo.create_table()
Produces a table with foo_field type set to decimal(10,5)
I was doing a query using annotate() (with the default Count aggregation) to order by the count of related models.
The query itself worked well but calling query.count() failed with:
OperationalError: no such column: count
Further investigation revealed that SelectQuery.count() replaces the query with bare COUNT(x) but doesn't remove the ORDER BY clause which in my case created an invalid statement referencing an unknown column alias.
Here's how I fixed it:
def count(self):
tmp = self.query, self._limit, self._offset, self._order_by
self._limit = self._offset = None
self._order_by = []
try:
if self.use_aliases():
self.query = 'COUNT(t1.%s)' % (self.model._meta.pk_name)
else:
self.query = 'COUNT(%s)' % (self.model._meta.pk_name)
res = self.database.execute(*self.sql())
finally:
# restore
self.query, self._limit, self._offset, self._order_by = tmp
return (res.fetchone() or [0])[0]
Two other things that I changed are:
Invalid method signature in Database class:
Is:
def sequence_exists(self):
raise NotImplementedError
Should be:
def sequence_exists(self, sequence_name):
raise NotImplementedError
If I create manually my table with a boolean type I can't insert a record using a field like BooleanField because it try to insert an integer.
Postgresql handles correctly bool() and stores a proper boolean field not like MySQL or Oracle which are casting in integer.
I sometimes get the following Failure:
======================================================================
FAIL: test_filter_both_directions (tests.FilterQueryTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/Users/diggy/Documents/dev/peewee/tests.py", line 1638, in test_filter_both_directions
self.assertSQLEqual(f.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN entrytag AS t2 ON t1.pk = t2.entry_id\nINNER JOIN blog AS t3 ON t1.blog_id = t3.id WHERE t2.tag = ? AND t3.title = ?', ['t1', 'b1']))
File "/Users/diggy/Documents/dev/peewee/tests.py", line 157, in assertSQLEqual
self.assertEqual(lhs[0].replace('?', interpolation), rhs[0].replace('?', interpolation))
AssertionError: 'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id\nINNER JOIN entrytag AS t3 ON t1.pk = t3.entry_id WHERE t2.title = ? AND t3.tag = ?' != 'SELECT t1.* FROM entry AS t1 INNER JOIN entrytag AS t2 ON t1.pk = t2.entry_id\nINNER JOIN blog AS t3 ON t1.blog_id = t3.id WHERE t2.tag = ? AND t3.title = ?'
----------------------------------------------------------------------
And always this one:
======================================================================
FAIL: test_count (tests.QueryTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/Users/diggy/Documents/dev/peewee/tests.py", line 495, in test_count
self.assertEqual(count, 200)
AssertionError: 240 != 200
----------------------------------------------------------------------
Ran 56 tests in 4.145s
I'm already working on these - but these seme really ODD actually. There is something interesting going on at this
for blog in SelectQuery(Blog):
print blog
for i in xrange(20):
self.create_entry(title='entry%d' % i, blog=blog)
Which outputs:
<Blog: a0>
<Blog: a1>
<Blog: a0>
<Blog: a1>
<Blog: a2>
<Blog: a3>
<Blog: a4>
<Blog: a5>
<Blog: a6>
<Blog: a7>
<Blog: a8>
<Blog: a9>
Given the following models:
class ModelA(model):
blah = CharField()
class ModelB(model):
modela = ForeignKeyField(ModelA)
blah = CharField()
Doing a query like this, gives results not quite expected:
modb = ModelB.get(id=1)
modas = ModelA.select().where(id__in=modb.modela)
The second query uses ModelB's primary key, instead of the modela_id column. The above example is sort of useless, as one could just use modb.modela, but when combined with Q():
modb = ModelB.get(id=1)
modas = ModelA.select().where(~Q(id__in=modb.modela))
The above would allow one to fetch all ModelA records that aren't related to ModelB.
Changing this would break the current behavior, but in my opinion it would make it behave more like the code actually reads.
What do you think?
Currently you are limited, more or less, to expressing select queries using the available abstractions - simple field queries, queries against column data (F objects), aggregations (min/max/count/etc), and aliases (foo as bar). To go beyond that requires using the RawQuery, which exposes a stripped down interface. Be nice to be able to add arbitrary elements to the select query to express things like "LOWER()" or other functions. Also be nice to add it to the where() clause to express things like CASE statements and the like.
Stuffing a None into any field returns a False value when retrieved. For example, setting a TextField to None returns '' (empty string), BooleanField returns False, etc..
Dunno if this intentional, (real NULL support would be nice, though IIRC django discourages it). If it is, this sentence in the docs is misleading: "Conversion between python and the database is handled transparently, including the proper handling of None/NULL." As a user of the dbapi directly, I expected storing a None to return a NULL.
This code:
from peewee import *
class Message(Model):
author = CharField()
body = TextField()
print Message.select().group_by('author').sql()
Throws this exception:
Traceback (most recent call last):
File "test.py", line 7, in <module>
print Message.select().group_by('author').sql()
File "peewee.py", line 1216, in sql
group_by = ', '.join(group_by)
TypeError: sequence item 0: expected string, tuple found
This part of SelectQuery.sql() seems responsible:
if self.use_aliases():
(...)
else:
group_by = [c[1] for c in self._group_by]
c[1] is a sequence of field names, not a single field name, so str.join() fails. The code used when use_aliases() returns True seems to work better.
I'm building a small site using peewee and bottle.py.
And I got Mysql error 2014 and 2006 frequently either develop env(windows) or product env(linux).
I connect mysql before every request, but don't close it. Does this matter?
Mysql error 2014 is "Commands out of sync; you can't run this command now" and 2006 is "MySQL server has gone away".
Is peewee caching any results?
I would love to see caching mechanisms here like in https://bitbucket.org/jmoiron/johnny-cache.
We're currently using the (hex/char) representation of UUIDs for a database, to use as primary keys. It looks like the current implementation requires Primary Key (and thus the .save() method) be an integer, and this is further checked through the isinstance call during creation of a model. It would be really great if we could use a TextField, or any field, as primary key.
Hi, I have the following model which have a datetime field with default value=datetime.now()
from datetime import datetime
class ATable(BaseModel):
time = DateTimeField(default=datetime.now())
customer = CharField()
Surprised when I create several records say:
ATable.create(customer='A')
# some delay
ATable.create(customer='B')
# some delay
ATable.create(customer='C')
The time (default) filled values was all the same: 2011-12-28 21:30:38.257 on all records
The time beeween record creation surelly in different time, at least microsecond and or second value
Perhaps a caching issue?
Thanks
Hi can you please add BETWEEN lookup type?
Thanks
To my knowledge, the sqlite3 module from the stdlib has the same API as the external pysqlite:
http://code.google.com/p/pysqlite
In case the sqlite3 module is absent, peewee could try to use pysqlite instead:
try:
import sqlite3
except ImportError:
try:
from pysqlite2 import dbapi2 as sqlite3
except ImportError:
sqlite3 = None
I'm trying to run a RawQuery that creates a view and involves several tables. Is there a way to do this in peewee? It seemed like RawQuery only accounts for simple cases tied to a single table in the data model. Is there a way to do a raw SQL query independent of the data model?
Thanks!
I have the following which I copied from some sample code:
from flask_peewee.auth import BaseUser
import datetime
from peewee import *
from app import db
class User(db.Model, BaseUser):
username = CharField(unique=True)
password = CharField()
email = CharField()
join_date = DateTimeField(default=datetime.datetime.now)
active = BooleanField(default=True)
admin = BooleanField(default=False)
def __unicode__(self):
return self.username
and the following config.py:
# Configuration for PostgreSQL
DATABASE = {
'name': 'whatever',
'engine': 'peewee.PostgresqlDatabase',
'user': 'me',
'password': 'mine'
}
but when I run this against Postgresql on Fedora Core 15, I get:
Traceback (most recent call last):
File "run_mine.py", line 7, in <module>
main.create_tables()
File "/home/watson/mine/main.py", line 15, in create_tables
User.create_table(fail_silently=True)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 1770, in create_table
cls._meta.database.create_table(cls)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 287, in create_table
self.execute(query, commit=True)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 252, in execute
res = cursor.execute(sql, params or ())
psycopg2.ProgrammingError: syntax error at or near "user"
LINE 1: CREATE TABLE user (username VARCHAR(255) NOT NULL, admin SMA...
It turns out that postgresql defines a user table by default and apparently its a reserved word. It was already there with my postgresql user already created in it before I even ran my flask app.
mine=> select * from user;
current_user
--------------
me
(1 row)
I'd recommend discouraging the use of "user" in any documentation relating to postgresql tables in peewee and also perhaps catching it and throwing a warning or error before it gets to the database driver. I tried making a simple change to my code to use users in place of user, but it was still blowing up in postgresql, though I'm not sure why. I'm open to advice here if you have any - I'm still intending to use peewee but Postgresql is a requirement.
Thanks!
I have a model where I do something simple:
class Whatever(db.Model):
field1 = CharField()
field2 = CharField()
field3 = CharField()
but in my view I'd like the query to behave like a SQL select. That is, I only get the columns back that I asked for in the select:
whatevers = Whatever.select(Whatever: ['id', "field2"]}) # at this point id and field2 are populated
columns=whatever.model._meta.get_field_names() # this gets me all the fields, not the fields matching the previous select
What I'm wondering is if there's a way to get the columns to match whatever's requested in the query. Obviously, I could do a list comprehension or the like to reduce the column list to the result set, but I'm looking for a more architectural solution - one that's supported within the model. It seemed like there would be something like your ModelConverter's only or exclude parameters, but I couldn't find an example where the select and the column list were matched in this way.
The use case here is simple: I want to be able to generate tables of data in my templates like:
<table>
<thead>
<tr>
{% for column in columns %}
<td>{{ column }}</td>
{% endfor %}
</tr>
</thead>
<tbody>
{% for whatever in whatevers %}
<tr>
{% for column in columns %}<td>{% if loop.index == 1: %}<a href="{{ url_for('detail', id=0) }}">{{ whatever[column] }}</a>{% else %}{{ whatever[column] }}{% endif %}</td>{% endfor %}
</tr>
{% endfor %}
</tbody>
</table>
Thanks!
in my model, cascade for mysql not work, peewee generate create table sql:
('CREATE TABLE space (id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(255));', None)
('CREATE UNIQUE INDEX space_id ON space(id);', None)
('CREATE TABLE page (id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, space_id INTEGER REFERENCES space (id) ON DELETE CASCADE, name VARCHAR(255));', None)
('CREATE INDEX page_space_id ON page(space_id);', None)
('CREATE UNIQUE INDEX page_id ON page(id);', None)
it looks like lack mysql FOREIGN KEY statements:
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.