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!