Sqlalchemy learning log _ MySQL

Sqlalchemy has recently learned the sqlalchemy framework, but I have not found an easy-to-understand blog post. after all, I am a newbie, I still cannot understand the world of great gods, and I just need to comment a few lines without programmers. After a whole day of Traceback and various errors, I finally found some opportunities, so I couldn't forget it again tomorrow morning.

Import

>>> From sqlalchemy import *
>>> From sqlalchemy. orm import *

Create a database engine

>>> Engine = create_engine ('MySQL: // root: 4QSJQCRC @ localhost/testdb', echo = True)

>>> Metadata = MetaData ()

# The format in create_engine () is: create_engine ('database: // database username: password @ host name/name of the database to use ', echo = True). echo here is not studied, make him = True anyway

# You can add the port number @ localhost: XXXX/testdb after the host name, which can also be omitted.

# All the articles I see on the Internet are taking the sqlite database as an example. I doubt whether the big ones are ctrl c or ctrl d. The built-in documents are also unclear. this step has been stuck for a long time.

# I don't know much about metadata. it is bound to the database engine. you can call some of the commands to perform operations on the database.

Define table

>>> Users_table = Table ('users', metadata,
... Column ('id', Integer, primary_key = True ),
... Column ('name', String (40 )),
... Column ('fullname', String (40 )),
... Column ('password', String (40 ))
...)

# Here is just a definition, rather than creating a table in the database.

# Remember the Table format. String is the varchar in SQL. online articles can be written directly to String without the length of characters. this is not acceptable on our machine, the following error occurs when writing a String directly:

# Sqlalchemy. exc. CompileError: (in table 'users', column 'name'): VARCHAR requires a length on dialect mysql

# So I added a length of 40

Create a table

>>> Metadata. create_all (engine)

23:57:28, 023 INFO sqlalchemy. engine. base. Engine show variables like 'SQL _ mode'
2014-07-18 23:57:28, 023 INFO sqlalchemy. engine. base. Engine ()
2014-07-18 23:57:28, 027 INFO sqlalchemy. engine. base. Engine select database ()
2014-07-18 23:57:28, 032 INFO sqlalchemy. engine. base. Engine ()
23:57:28, 034 INFO sqlalchemy. engine. base. Engine show collation where 'charset' = 'utf8' and 'colation' = 'utf8 _ bin'
2014-07-18 23:57:28, 034 INFO sqlalchemy. engine. base. Engine ()
23:57:28, 041 INFO sqlalchemy. engine. base. Engine select cast ('test plain returns' as char (60) AS anon_1
2014-07-18 23:57:28, 042 INFO sqlalchemy. engine. base. Engine ()
23:57:28, 051 INFO sqlalchemy. engine. base. Engine select cast ('test unicode returns' as char (60) AS anon_1
2014-07-18 23:57:28, 051 INFO sqlalchemy. engine. base. Engine ()
23:57:28, 052 INFO sqlalchemy. engine. base. Engine select cast ('test collated returns 'as char character set utf8) COLLATE utf8_bin AS anon_1
2014-07-18 23:57:28, 052 INFO sqlalchemy. engine. base. Engine ()
23:57:28, 054 INFO sqlalchemy. engine. base. Engine DESCRIBE 'users'
2014-07-18 23:57:28, 056 INFO sqlalchemy. engine. base. Engine ()
2014-07-18 23:57:28, 059 INFO sqlalchemy. engine. base. Engine ROLLBACK
2014-07-18 23:57:28, 061 INFO sqlalchemy. engine. base. Engine
Create table users (
Id integer not null AUTO_INCREMENT,
Name VARCHAR (40 ),
Fullname VARCHAR (40 ),
Password VARCHAR (40 ),
Primary key (id)
)


2014-07-18 23:57:28, 062 INFO sqlalchemy. engine. base. Engine ()
2014-07-18 23:57:28, 074 INFO sqlalchemy. engine. base. Engine COMMIT

# This step creates the 'users' table in the database '. Note: in the database testdb, the database testdb is specified when the engine is set up at the beginning.

Define a class that matches the users table.

>>> Class User (object ):
... Def _ init _ (self, name, fullname, password ):
... Self. name = name
... Self. fullname = fullname
... Self. password = password
... Def _ repr _ (self ):

... Return" "% (Self. name, self. fullname, self. password)

#__ The attributes in init _ must correspond to the created table. the User class is equivalent to the table template, and the class instance is a row in the table.

# The number of objects in the class User (object) cannot be small. I don't know why an error occurs if the brackets are empty on my machine.

#__ Repr _ is only used for testing and observation.

Ing

>>> Mapper (User, users_table)

# In this step, we associate the table with the class one by one.

Create transactions and bind database connections

>>> Session = sessionmaker (bind = engine)

>>> Session = Session ()

# Session is a transaction. it maintains a database link before commit and close.

Add a class instance to a transaction

>>> Session. add_all ([
... User ('Wendy ', 'Wendy Williams', 'foobar '),
... User ('Mary ', 'Mary Contrary', 'xxg527 '),
... User ('Fred ', 'Fred Flinstone', 'blah')])

# Three User-like instances are created respectively as the users rows in the table and added to the transaction. at this time, the transaction has not been committed, and these rows have not been created in the database.

Commit transactions

>>> Session. commit ()
01:04:46, 359 INFO sqlalchemy. engine. base. Engine BEGIN (implicit)
2014-07-19 01:04:46, 362 INFO sqlalchemy. engine. base. Engine insert into users (name, fullname, password) VALUES (% s, % s, % s)
01:04:46, 362 INFO sqlalchemy. engine. base. Engine ('Wendy ', 'Wendy Williams', 'foobar ')
2014-07-19 01:04:46, 365 INFO sqlalchemy. engine. base. Engine insert into users (name, fullname, password) VALUES (% s, % s, % s)
01:04:46, 366 INFO sqlalchemy. engine. base. Engine ('Mary ', 'Mary Contrary', 'xxg527 ')
2014-07-19 01:04:46, 367 INFO sqlalchemy. engine. base. Engine insert into users (name, fullname, password) VALUES (% s, % s, % s)
01:04:46, 367 INFO sqlalchemy. engine. base. Engine ('Fred ', 'Fred Flinstone', 'blah ')
2014-07-19 01:04:46, 368 INFO sqlalchemy. engine. base. Engine COMMIT

# The users table in the database has these three rows

Query

>>> Our_user = session. query (User). filter_by (name = 'Mary '). first ()
01:08:38, 624 INFO sqlalchemy. engine. base. Engine BEGIN (implicit)
2014-07-19 01:08:38, 626 INFO sqlalchemy. engine. base. Engine SELECT users. id AS users_id, users. name AS users_name, users. fullname AS users_fullname, users. password AS users_password
FROM users
WHERE users. name = % s
LIMIT % s
01:08:38, 627 INFO sqlalchemy. engine. base. Engine ('Mary ', 1)
>>> Our_user


# Query the first row with name 'Mary 'in the table. the returned data is in our_user.

These are the basic content, so easy. OK, go to bed