Python ORM framework SQLObject entry instance

Source: Internet
Author: User

SQLObject and SQLAlchemy are both ORM (Object relationship ing) solutions in the Python language. SQLAlchemy is considered as the de facto ORM standard in Python. Of course, both are excellent.

I. Installation
Copy codeThe Code is as follows: sudo pip install SQLObject
When using SQLObject to operate mysql, the following error occurs: ImportError: No module named MySQLdb. then install MySQLdb:
Copy codeThe Code is as follows: sudo pip install MySQL-python
I did not expect another error:
Copy codeThe Code is as follows:
_ Mysql. c: 29: 20: fatal error: Python. h: No such file or directory
Compilation terminated.
Error: command 'x86 _ 64-linux-gnu-gcc 'failed with exit status 1
Solution:
Copy codeThe Code is as follows:
Sudo apt-get install libmysqlclient-dev python-dev

Ii. Use it to create a table

Change the encoding of the default test database in mysql to UTF-8.
Copy codeThe Code is as follows: #-*-encoding: UTF-8 -*-
From sqlobject import *

Uri = r'mysql: // root: passwd@127.0.0.1/test? Charset = utf8'
Sqlhub. processConnection = connectionForURI (uri)

Class User (SQLObject ):
Name = StringCol (length = 10, notNone = True)
Email = StringCol (length = 20, notNone = True)
Password = StringCol (length = 20, notNone = True)

User. createTable ()

After running the command, the user table is displayed in the test database. Use show create table user; to view the creation statement of the user table. The result is as follows:
Copy codeThe Code is as follows:
Create table 'user '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (10) not null,
'Email 'varchar (20) not null,
'Password' varchar (20) not null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8

3. Add/delete records

Now we try to add and delete records.
Copy codeThe Code is as follows:
User1 = User (name = 'user1', email = 'user1 @ 163.com ', password = '20140901 ')
User2 = User (name = 'user2', email = 'user2 @ 163.com ', password = '20140901 ')

After running, use select * from user to view the two records:
Copy codeThe Code is as follows:
Mysql> select * from user;
+ ---- + ------- + --------------- + ---------- +
| Id | name | email | password |
+ ---- + ------- + --------------- + ---------- +
| 1 | user1 | user1@163.com | 111 |
| 2 | user2 | user2@163.com | 222 |
+ ---- + ------- + --------------- + ---------- +
2 rows in set (0.00 sec)

Delete data

Copy codeThe Code is as follows:
U2 = User. get (2)
Print User. delete (u2.id)

Iv. query records


Get data by id:
Copy codeThe Code is as follows:
U1 = User. get (1)
U1_1 = User. get (1)
U2 = User. get (2)
Print id (u1), u1
Print id (u1_1), u1_1
Print id (u2), u2
Output result:
Copy codeThe Code is as follows:
23864656
23864656
23930512
Because id (u1) and id (u1_1) are equal, the content of u1 and u1_1 is consistent, which can reduce memory usage. You can set parameters when connecting to the database to disable this method.

Query by name:
Copy codeThe Code is as follows:
Users = User. select (User. q. name = "user1 ")
Print users
Print list (users)
Output result:
Copy codeThe Code is as follows:
SELECT user. id, user. name, user. email, user. password FROM user WHERE (user. name) = ('user1 '))
[]
Fuzzy query:
Copy codeThe Code is as follows:
Users = User. select (User. q. name. startswith ('U '))
Print users
Print list (users)
Users = User. select (User. q. name. contains ('ser1 '))
Print users
Print list (users)
Running result:
Copy codeThe Code is as follows:
SELECT user. id, user. name, user. email, user. password FROM user WHERE (user. name LIKE ('U % ') ESCAPE '\\')
[,]
SELECT user. id, user. name, user. email, user. password FROM user WHERE (user. name LIKE ('% ser1 %') ESCAPE '\\')
[]

One-to-multiple ing

Create a new table and save the articles written by each user in the user:
Copy codeThe Code is as follows: class User (SQLObject ):
Name = StringCol (length = 10, notNone = True)
Email = StringCol (length = 20, notNone = True)
Password = StringCol (length = 20, notNone = True)

Class Article (SQLObject ):
Title = StringCol (length = 100, notNone = True)
Content = StringCol (notNone = True)
User = ForeignKey ('user ')
Article. createTable ()
After running the command, use show create table article to view the creation statement:
Copy codeThe Code is as follows:
Create table 'Article '(
'Id' int (11) not null AUTO_INCREMENT,
'Title' varchar (100) not null,
'Content' text not null,
'User _ id' int (11) default null,
Primary key ('id '),
KEY 'Article _ user_id_exists '('user _ id '),
CONSTRAINT 'Article _ user_id_exists 'foreign key ('user _ id') REFERENCES 'user' ('id ')
) ENGINE = InnoDB default charset = utf8
Add data:
Copy codeThe Code is as follows:
U1 = User. get (1)
A1 = Article (title = 'title1', content = '', user = u1)
Query data:
Copy codeThe Code is as follows:
U1 = User. get (1)
A1 = Article. select (Article. q. user = u1)
Print a1
Print list (a1)
Print list (a1) [0]. content
This method can also be:
Copy codeThe Code is as follows:
A1 = Article. select (Article. q. userID = 1)
Print a1
Print list (a1)
Print list (a1) [0]. content
Running result:
Copy codeThe Code is as follows:
SELECT article. id, article. title, article. content, article. user_id FROM article WHERE (article. user_id) = (1 ))
[<Article title = 'title1 '>]
Hi!

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.