Python's ORM framework SQLObject starter instance

SQLObject and SQLAlchemy are both ORM (object relational mapping) solutions in Python, where SQLAlchemy is considered the DE facto ORM standard in Python. Of course, both are excellent.

A, install,

sudo pip install SQLObject

Using SQLObject to operate mysql raises an error: ImportError: No module named MySQLdb, which installs MySQLdb:

sudo pip install MySQL-python

I didn't expect to report an error again:

_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

Solutions:

sudo apt-get install libmysqlclient-dev python-dev

Use it to create tables

Change the encoding of mysql's default test database to utf-8.

#-*-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, we will see a table user under the test database. We will use show create table user. Look at the create statement for the user table and the result 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

Add/delete records

Now we try to add and remove records.

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

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

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 the data

u2 = User.get(2)
print User.delete(u2.id)

Query records

Get data by id:

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:

23864656 
23864656 
23930512

Since id(u1) and id(u1_1) are equal, u1 and u1_1 are identical in content, which reduces memory usage. You can disable this mode by setting parameters when connecting to the database.

Query according to name:

users = User.select(User.q.name=="user1")
print users
print list(users)

Output results:

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

Fuzzy query:

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

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 mapping

We create a new table to save the articles written by each user in user:

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 create statement:

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:

u1 = User.get(1)
a1 = Article(title='title1',content=' hello ',user=u1)

Query data:

u1 = User.get(1)
a1 = Article.select(Article.q.user == u1)
print a1
print list(a1)
print list(a1)[0].content

This also works:

a1 = Article.select(Article.q.userID == 1)
print a1
print list(a1)
print list(a1)[0].content

Operation results:

SELECT article.id, article.title, article.content, article.user_id FROM article WHERE ((article.user_id) = (1))
[<Article title='title1'>]
 hello