from pmorm import Mysql
mydb = Mysql('localhost', 'root', 'your-passwd', 'testdb')
Create a model and create the table
from pmorm import Base, PrimaryKeyField, VarcharField, DoubleField
# Model classclassUser(Base):
# Built-in class Meta for configuring database and tableclassMeta:
db = mydb
table ='user'# Define fields in a modelid= PrimaryKeyField() # id field must be defined like this
username = VarcharField(max_length=32, nullable=False, unique=True, default=None)
password = VarcharField(max_length=64, nullable=False, unique=False, default=None)
balance = DoubleField(nullable=False, unique=True, default=0.0)
# Create table if it hasn't been created
User.create_table()
Insert
# A easy way
user1 = User(username='user1', password='passwd1')
user1.insert()
# Another way
user2 = User()
user2.username ='user2'
user2.password ='passwd2'
user2.balance =3000.0
user2.insert()
# You can modify it before inserting
user3 = User(username='userx')
user3.username ='user3'
user3.password ='passwd3'
user3.insert()
# Cheak if objects has been insertedprint(user1.inserted()) # True
Search
Get all
users = User.search().all()
for user in users:
print("id:{} username:{} password:{} balance:{}".format(user.id, user.username, user.password, user.balance))
Search all by one condition
users = User.search(User.username !='unkonwn').all()
for user in users:
print("id:{} username:{} password:{} balance:{}".format(user.id, user.username, user.password, user.balance))
Search by combination queries
# By using | and & operators
user1 = User.search(
(User.username=='user1') & (User.password=='passwd1')
).first()
"""Attention: - The code below is the same as the result above - The difference:`first()` function selects only the first,it's faster - But `all()[0]` gets all the first and then take the first one,it's sloweruser1 = User.search( (User.username=='user1') & (User.password=='passwd1')).all()[0]In conclusion: - If you only need to get the first one, use `first()` - If you need to get all, use `all()`"""print("id:{} username:{} password:{} balance:{}".format(user1.id, user1.username, user1.password, user1.balance))
Sort by using the "orders" option
users = User.search(
(User.username!='user1') | (User.password!='passwd1'),
orders=[-User.id] # Sort by id in reverse order
).all()
for user in users:
print("id:{} username:{} password:{} balance:{}".format(user.id, user.username, user.password, user.balance))
Using the limit
users = User.search(User.username!='unknown').all(limit=(0,2)) # limit only returns the first two results of the query, equivalent to "LIMIT 0, 2"# Equivalent to:`users = User.search(User.username!='unknown').all(limit=(2))`for user in users:
print("id:{} username:{} password:{} balance:{}".format(user.id, user.username, user.password, user.balance))
Update
# Get one first
user1 = User.search(
((User.username=='user1') | (User.password=='passwd1') & (User.id==1)) # Complex queries
).first()
print("id:{} username:{} password:{}".format(user1.id, user1.username, user1.password))
# Edit it and update
user1.username ='edit'
user1.update()
print("id:{} username:{} password:{} balance:{}".format(user1.id, user1.username, user1.password, user1.balance))
Delete
# Get one first
user1 = User.search(User.username=='edit').first()
# Delete it
user1.delete()
Else
Currently supported MySQL fields
Pmorm
Mysql
PrimaryKeyField
NO
BooleanField
BOOLEAN
IntField
INT
BigIntField
BIGINT
FloatField
FLOAT
DoubleField
DOUBLE
VarcharField
VARCHAR
TextField
TEXT
PrimaryKeyField must be defined in each model, so a basic model looks like...
mydb = Mysql('localhost', 'root', 'your-passwd', 'your-database')
classModelName(Base):
classMeta:
db = mydb
table ='mytable'id= PrimaryKeyField()
# Other fields...