Both Sqlobject and SQLAlchemy are ORM (Object Relational mapping) solutions under the Python language, where SQLAlchemy is considered the de facto ORM standard under Python. Of course, both are excellent.
First, installation
Copy Code code as follows:
sudo pip install Sqlobject
Use sqlobject operation MySQL error importerror:no module named MySQLdb, then install MYSQLDB:
Copy Code code as follows:
sudo pip install Mysql-python
Did not expect to complain again:
Copy Code code 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
Workaround:
Copy Code code as follows:
sudo apt-get install Libmysqlclient-dev Python-dev
Ii. Use it to create a table
Change the encoding of the test database for MySQL defaults to Utf-8.
Copy Code code 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, you will see the table user appears under the test database, and we use show create table user to view the creation statement of the user table with the following results:
Copy Code code as follows:
CREATE TABLE ' user ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar not NULL,
' Email ' varchar not NULL,
' Password ' varchar not NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8
Third, add/delete records
Now we're trying to add and delete records.
Copy Code code as follows:
User1 = User (name= ' user1 ', email= ' user1@163.com ', password= ' 111 ')
User2 = User (name= ' user2 ', email= ' user2@163.com ', password= ' 222 ')
After running, you can see both records using the SELECT * from User:
Copy Code code 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 Code code as follows:
U2 = User.get (2)
Print User.delete (u2.id)
Four, the inquiry record
Get data by ID:
Copy Code code 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 results:
Copy Code code as follows:
23864656
23864656
23930512
Because IDs (U1) and IDs (u1_1) are equal, U1 and u1_1 are consistent in content, which can reduce memory usage. You can set parameters when you connect to a database, prohibiting this method.
To query by name:
Copy Code code as follows:
Users = User.select (user.q.name== "user1")
Print Users
Print List (users)
Output results:
Copy Code code as follows:
SELECT user.id, User.Name, User.email, User.password from User WHERE ((user.name) = (' user1 '))
[]
Fuzzy query:
Copy Code code 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)
Run Result:
Copy Code code 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-many Mappings
We create a new table that holds the articles written by each user in users:
Copy Code code 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, use the show CREATE table article to view the creation statement:
Copy Code code as follows:
CREATE TABLE ' article ' (
' id ' int (one) not NULL auto_increment,
' title ' varchar Not NULL,
' Content ' text not NULL,
' user_id ' int (one) 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 Code code as follows:
u1 = User.get (1)
A1 = Article (title= ' title1 ', content= ' hello ', user=u1)
Query data:
Copy Code code as follows:
u1 = User.get (1)
A1 = Article.select (Article.q.user = = u1)
Print A1
Print List (A1)
Print List (A1) [0].content
This approach can also be:
Copy Code code as follows:
A1 = Article.select (Article.q.userid = 1)
Print A1
Print List (A1)
Print List (A1) [0].content
Run Result:
Copy Code code as follows:
SELECT article.id, Article.title, Article.content, article.user_id from Article WHERE ((article.user_id) = (1))
[<article title= ' Title1 ']
How are you doing
