from pmorm import Mysql

mydb = Mysql('localhost', 'root', 'your-passwd', 'testdb')

from pmorm import Base, PrimaryKeyField, VarcharField, DoubleField

# Model class
class User(Base):
    # Built-in class Meta for configuring database and table
    class Meta:
        db = mydb
        table = 'user'

    # Define fields in a model
    id = 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()

# 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 inserted
print(user1.inserted()) # True

users = User.search().all()

for user in users:
    print("id:{} username:{} password:{} balance:{}".format(user.id, user.username, user.password, user.balance))

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))

# 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 slower

user1 = 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))

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))

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))

# 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))

# Get one first
user1 = User.search(User.username=='edit').first()
# Delete it
user1.delete()

mydb = Mysql('localhost', 'root', 'your-passwd', 'your-database')
class ModelName(Base):
    class Meta:
        db = mydb
        table = 'mytable'
    id = PrimaryKeyField()
    # Other fields...