Python's ORM Framework Sqlobject Getting Started instance

Both Sqlobject and SQLAlchemy are ORM (object-relational mapping) solutions under the Python language, where SQLAlchemy is considered to be the de facto ORM standard under Python. Of course, both are excellent.

First, installation

The code is as follows:

sudo pip install Sqlobject

Use Sqlobject to operate MySQL error Importerror:no module named MySQLdb, then install MYSQLDB:

The code is as follows:

sudo pip install Mysql-python

Did not expect another error:

The 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

Workaround:

The code is as follows:

sudo apt-get install Libmysqlclient-dev Python-dev

second, use it to create a table

Change the encoding of the test database with MySQL default to Utf-8.

The 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, you will see the table user appears under the test database, we use show create table user, and look at the user table creation statement, the result is as follows:

The code is as follows:

CREATE TABLE ' user ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (ten) is 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.

The code is as follows:

User1 = User (name= ' user1 ', email= ' user1@163.com ', password= ' 111 ')
User2 = User (name= ' user2 ', email= ' user2@163.com ', password= ' 222 ')

After running, use the SELECT * from user to see these two records:

The 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

The code is as follows:

U2 = User.get (2)
Print User.delete (u2.id)

Iv. Records of inquiries

Get data by ID:

The 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:

The code is as follows:

23864656
23864656
23930512

Because the ID (U1) and ID (u1_1) are equal, U1 and u1_1 are content-consistent, which can reduce memory usage. You can set parameters when you connect to the database, and prohibit this mode.

Query by name:

The code is as follows:

Users = User.select (user.q.name== "user1")
Print Users
Print List (users)

Output Result:

The code is as follows:

SELECT user.id, User.Name, User.email, User.password from User WHERE ((user.name) = (' user1 '))
[]

Fuzzy query:

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

Operation Result:

The 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-many mappings

We create a new table that holds each user's authored article:

The 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, view the CREATE statement using show CREATE TABLE article:

The code is as follows:

CREATE TABLE ' article ' (
' id ' int (one) not NULL auto_increment,
' title ' varchar (+) not NULL,
' Content ' text is 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:

The code is as follows:

u1 = User.get (1)
A1 = article (title= ' Title1 ', content= ' hello ', user=u1)

Query data:

The 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 way you can also:

The code is as follows:

A1 = Article.select (Article.q.userid = = 1)
Print A1
Print List (A1)
Print List (A1) [0].content

Operation Result:

The code is as follows:

SELECT article.id, Article.title, Article.content, article.user_id from Article WHERE ((article.user_id) = (1))
[]
How are you doing

