Python uses mysql in two ways: pythonmysql

Python uses mysql in two ways: pythonmysql

Python is used to operate MySQL in two ways:

• Native module pymsql
• ORM framework SQLAchemy

Pymql

Pymsql is a module used to operate MySQL in Python. It is installed in windows:

pip install pymysql

Entry: connect to mysql in centos In the VM and query the data in the student table in the test database.

Import pymysql # create a connection conn = pymysql. connect (host = '2017. 168.123.207 ', port = 3306, user = 'root', passwd = 'root', db = "test"); # create a cursor = conn. cursor () # Run the SQL statement and return the affected number of rows. Export t_row = cursor.exe cute ("select * from student") print (export t_row)

Running result:

 

Permission required

mysql> grant all on *.* to 'root'@'%' identified by 'root';Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)

At this time, we can see that the program runs successfully.

 

This indicates that five pieces of data have been queried. If we need to check the five pieces of specific data, we need:

print(cursor.fetchone())

Cursor. fetchone () is a piece of data to get it out.

 

Here we use twocursor.fetchone()

If we want to retrieve only the first three data records:

print('>>>>>',cursor.fetchmany(3))

 

Retrieve all data at a time:

print('------',cursor.fetchall())

 

If we want to insert multiple data records:

Import pymysql # create a connection conn = pymysql. connect (host = '2017. 168.123.207 ', port = 3306, user = 'root', passwd = 'root', db = "test"); # create a cursor = conn. cursor () data = [("N1", 23, "2015-01-01", "M"), ("N2", 24, "2015-01-02", "F "), ("N3", 25, "2015-01-03", "M"),] # execute the SQL statement, and return the number of affected rows. Then t_row = cursor.exe cute.pdf ("insert into student (name, age, register_date, gender) values (% s, % s) ", data) conn. commit ()

 

Note: executecommit starts a transaction by default. Without conn. commit (), data will not be inserted successfully.

Sqlalchemy ORM

1. ORM Introduction

Orm stands for object relational ing, which is an object ing program. In simple terms, object-oriented programs like python are all objects. We instantiate an object, call the functions in the form of vertices. Orm is equivalent to instantiating a database. The database is relational. Through orm, the object model of the programming language is mapped with the relational model of the database, in this way, you can directly use the object model of the programming language to operate the database, instead of using the SQL language directly.

Advantages:

1. Hiding data access details, "closed" universal database interaction, and the core of ORM. It makes the interaction between General databases easy and easy, and does not need to consider damn SQL statements.

2. ORM makes it easy to construct and solidify the data structure.

2. Install sqlalchemy

Installation:

pip install sqlalchemy

 

3. basic use of sqlalchemy

First, let's take a look at how we created a data table before using the orm:

create table student( id int auto_increment, name char(32) not null, age int not null, register_date date not null, primary key(id) );

Use orm to create the above table. The Code is as follows:

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringengine = create_engine ("mysql + pymysql: // root: root@192.168.123.207/test", encoding = 'utf-8', echo = True) base = declarative_base () # generate an orm Base class User (Base): _ tablename _ = 'user' # table name id = Column (Integer, primary_key = True) name = Column (String (32) password = Column (String (64) Base. metadata. create_all (engine) # create a table structure

 

Use orm to create a data record:

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy. orm import sessionmakerengine = create_engine ("mysql + pymysql: // root: root@192.168.123.207/test", encoding = 'utf-8', echo = True) Base = declarative_base () # generate an orm Base class User (Base): _ tablename _ = 'user' # table name id = Column (Integer, primary_key = True) name = Column (String (32) password = Column (String (64) Base. metadata. create_all (engine) # create a table structure Session_class = sessionmaker (bind = engine) # create a session class with the database. Note that the session class is returned to the session here, not instance Session = Session_class () # generate session instance user_obj = User (name = "xiaoming", password = "123456 ") # generate the data object user_obj2 = User (name = "jack", password = "123564") # generate the data object print (user_obj.name, user_obj.id) You want to create) # No object has been created yet. Print the id or NoneSession. add (user_obj) Session. add (user_obj2) print (user_obj.name, user_obj.id) # No

Session. commit () # submit and create data only now

To insert data, use sessionmaker to create a class by binding the connection created above, generate a session instance (equivalent to the previous cursor), create two records in the object-oriented way, and then add, the final commit is enough.

Next let's look at the addition, deletion, modification, and query of the database:

Query:

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy. orm import sessionmakerengine = create_engine ("mysql + pymysql: // root: root@192.168.123.207/test", encoding = 'utf-8') Base = declarative_base () # generate an orm Base class User (Base): _ tablename _ = 'user' # table name id = Column (Integer, primary_key = True) name = Column (String (32) password = Column (String (64) Base. metadata. create_all (engine) # create a table structure Session_class = sessionmaker (bind = engine) # create a session class with the database. Note that the session class is returned to the session here, not instance Session = Session_class () # generate session instance data = Session. query (User ). filter_by (name = "xiaoming "). all () # print (data [0]. name, data [0]. password) print (data)

If onlySession.query(User).filter_by(name="xiaoming"),Only one SQL statement is displayed:

Filter_by () is a list of data.

Add. all ()

 

This is an object, which cannot be seen, so we need to manually call up the data

We useprint(data[0].name,data[0].password):

 

In this way, the data is found.

Check a piece of data. If no data is written in filter_by:

data=Session.query(User).filter_by().all()

 

We have found several pieces of data, and we need to cycle to see the specific data. What should we do if we want to directly see who we are?

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy. orm import sessionmakerengine = create_engine ("mysql + pymysql: // root: root@192.168.123.207/test", encoding = 'utf-8') Base = declarative_base () # generate an orm Base class User (Base): _ tablename _ = 'user' # table name id = Column (Integer, primary_key = True) name = Column (String (32) password = Column (String (64) def _ repr _ (self): return "<% s name: % s> "% (self. id, self. name) Base. metadata. create_all (engine) # create a table structure Session_class = sessionmaker (bind = engine) # create a session class with the database. Note that the session class is returned to the session here, not instance Session = Session_class () # generate session instance data = Session. query (User ). filter_by (). all () print (data)

We added the _ repr _ () function to check the running result:

 

In this way, the query results are displayed. This is equivalent to setting in the _ repr _ function if you want to view what data and display it in what format.

Here, we usefilter_by().all()To query all the data, we usefilter_by().first() To query the first data record in the database.

We usefilter_by(name="xiaoming")After querying the data with the name xiaoming, how can we query the data with the user ID> 1?

data=Session.query(User).filter(User.id>1).all()

Multiple conditional queries: add several filters.

data=Session.query(User).filter(User.id>1).filter(User.id<3).all()

Modify:

data=Session.query(User).filter(User.id>1).first()print(data)data.name = "Jack Chen"data.password = "555555"Session.commit()

Query the data you want to modify, and then modify the data in the same way as the data in the object-oriented method. Finally, commit () is enough.

 

Delete:

data = Session.query(User).filter_by(name = 'Rain').first()Session.delete(data)Session.commit()

Similarly, first query the data to be deleted, then delete the data, and finally submit the commit

Rollback:

Fake_user = User (name = 'rain', password = "123456") Session. add (fake_user) print (Session. query (User ). filter (User. name. in _ (['jack', 'rain']). all () # in this case, check that the added data session exists in the Session. rollback () # at this time you rollback print (Session. query (User ). filter (User. name. in _ (['jack', 'rain']). all () # Check again and find that the newly added data is gone.

Check the running result:

 

At this time, we can see that we can see the data just inserted at the beginning, but we will not see it after rollback. Let's go to the database to see it:

 

Let's insert a piece of data.

 

We can see that the id of the inserted data is 4. It seems that the data is inserted first, then rolled back and deleted.

Group statistics:

Statistics:

Session.query(User).filter(User.name.in_(['xiaoming','rain'])).count()

GROUP:

from sqlalchemy import funcprint(Session.query(func.count(User.name),User.name).group_by(User.name).all())

 

Join multi-Table query:

Session. query (User, Student ). filter (User. id = Student. id ). all () Session. query (User ). join (Student ). all () # The two tables must be associated with foreign keys.

 

Foreign key Association

Create two tables, student and study_record:

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, String, DATE, ForeignKeyfrom sqlalchemy. orm import sessionmakerengine = create_engine ("mysql + pymysql: // root: root@192.168.123.207/xumingdb", encoding = 'utf-8') Base = declarative_base () # generate an orm Base class Student (Base): _ tablename _ = 'student '# table name id = Column (Integer, primary_key = True) name = Column (String (32), nullable = False) register_date = Column (DATE, nullable = False) def _ repr _ (self): return "<% s name: % s> "% (self. id, self. name) class StudyRecord (Base): _ tablename _ = 'Study _ record '# table name id = Column (Integer, primary_key = True) day = Column (Integer, nullable = False) status = Column (String (32), nullable = False) stu_id = Column (Integer, ForeignKey ("student. id ") def _ repr _ (self): return" <% s day: % s> "% (self. id, self. day) Base. metadata. create_all (engine) # create a table structure

Create a foreign key table.ForeignKey("student.id")It is directly the table name. Field name.

Insert data into the two tables:

Base. metadata. create_all (engine) # create table structure Session_class = sessionmaker (bind = engine) session = Session_class () s1 = Student (name = "xiaoming", register_date = "2015-06-07 ") s2 = Student (name = "huahua", register_date = "2015-06-08") s3 = Student (name = "caicai", register_date = "2015-06-09 ") s4 = Student (name = "zhazha", register_date = "2015-06-04") study_obj1 = StudyRecord (day = 1, status = "YES", stu_id = 1) study_obj2 = StudyRecord (day = 2, status = "NO", stu_id = 1) study_obj3 = StudyRecord (day = 3, status = "YES", stu_id = 1) study_obj4 = StudyRecord (day = 1, status = "YES", stu_id = 2) session. add_all ([s1, s2, s3, s4]) session. add_all ([study_obj1, study_obj2, study_obj3, study_obj4]) session. commit ()

Add_all is used for batch insertion, and a list is used to insert the data to be inserted. Note that because a foreign key is set up, when adding data, study_record data must be inserted only after the student table data is inserted. If you insert data together, an error is returned.

Now we want to know how many courses xiaoming has taken:

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, String, DATE, ForeignKeyfrom sqlalchemy. orm import sessionmaker, relationshipengine = create_engine ("mysql + pymysql: // root: root@192.168.123.207/xumingdb", encoding = 'utf-8') Base = declarative_base () # generate an orm Base class Student (Base): _ tablename _ = 'student '# table name id = Column (Integer, primary_key = True) name = Column (String (32), nullable = False) register_date = Column (DATE, nullable = False) def _ repr _ (self): return "<% s name: % s> "% (self. id, self. name) class StudyRecord (Base): _ tablename _ = 'Study _ record '# table name id = Column (Integer, primary_key = True) day = Column (Integer, nullable = False) status = Column (String (32), nullable = False) stu_id = Column (Integer, ForeignKey ("student. id ") student = relationship (" Student ", backref =" my_study_record ") def _ repr _ (self): return" <% s day: % s status: % s> "% (self. student. name, self. day, self. status) Base. metadata. create_all (engine) # create the table structure Session_class = sessionmaker (bind = engine) session = Session_class () stu_obj = session. query (Student ). filter (Student. name = 'xiaoming '). first () print (stu_obj.my_study_record) session. commit ()

Note the statement marked red in the above Code. We introduce relationship, and then this allows you to use the backref field in the study_record table to reverse locate all its associated items in the student table, A two-way query is implemented, that is, to associate the student table. In the studyrecord, all fields in the student table can be queried through the student field. In turn, in the student table, we can use the my_study_record field to reverse query the data in studyrecord, which then represents the xiaoming we found below. my_study_record can be used to query data items with xiaoming IDs in the studyrecord table.

Multi-foreign key Association

First, we create two tables customer and address. The customer table has two foreign keys for address:

Import sqlalchemyfrom sqlalchemy import Integer, ForeignKey, String, Columnfrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy. orm import relationshipfrom sqlalchemy import create_engineengine = create_engine ("mysql + pymysql: // root: root@192.168.123.207/xumingdb", encoding = 'utf-8') Base = declarative_base () class Customer (Base): _ tablename _ = 'customer' id = Column (Integer, primary_key = True) name = Column (String (64) billing_address_id = Column (Integer, foreignKey ("address. id ") shipping_address_id = Column (Integer, ForeignKey (" address. id ") billing_address = relationship (" Address ") shipping_address = relationship (" Address ") class Address (Base ): _ tablename _ = 'address' id = Column (Integer, primary_key = True) street = Column (String (64) city = Column (String (64 )) state = Column (String (64) def _ repr _ (self): return self. streetBase. metadata. create_all (engine) # create a table structure

Then we insert data into the two tables:

from day12 import sqlalchemy_multi_fkfrom sqlalchemy.orm import sessionmakerSession_class = sessionmaker(bind=sqlalchemy_multi_fk.engine)session = Session_class()addr1 = sqlalchemy_multi_fk.Address(street="XiZangBeiLu",city="ShangHai",state="ShangHai")addr2 = sqlalchemy_multi_fk.Address(street="YuHu",city="XiangTan",state="HuNan")addr3 = sqlalchemy_multi_fk.Address(street="ZhongXinZhen",city="SuQian",state="JiangSu")session.add_all([addr1,addr2,addr3])c1 = sqlalchemy_multi_fk.Customer(name="xiaoming",billing_address=addr1,shipping_address=addr2)c2 = sqlalchemy_multi_fk.Customer(name="jack",billing_address=addr3,shipping_address=addr3)session.add_all([c1,c2])session.commit()

In this way, an error occurs during insertion:

Sqlalchemy. exc. ambiguousForeignKeysError: cocould not determine join condition between parent/child tables on relationship Customer. billing_address-there are multiple foreign key paths linking the tables. specify the 'Foreign _ keys 'argument, providing a list of those columns which shoshould be counted as containing a foreign key reference to the parent table.

To put it bluntly, when we are doing a join query, two fields are associated with the Address table at the same time. During reverse query, it cannot tell who is who, during reverse query by address, we cannot tell which field represents billing_address, and which field represents shipping_address. how can we solve this problem?

billing_address = relationship("Address",foreign_keys=[billing_address_id])shipping_address = relationship("Address",foreign_keys=[shipping_address_id])

Addforeign_keysThe parameter is enough.

 

Data added !!

In this case, we need to query the customer address:

obj = session.query(sqlalchemy_multi_fk.Customer).filter(sqlalchemy_multi_fk.Customer.name=="xiaoming").first()print(obj.name,obj.billing_address,obj.shipping_address)

 

Many-to-many relationship

Now let's design a table structure that describes the relationship between "books" and "Authors". The requirements are:

1. A book can be published by several authors.

2. One author can write several books.

First, let's take a look at the general idea:

 

Two tables. When we encounter a book where multiple authors participate in the publication, we write the Author id together, but this is not conducive to queries.

Then we can add another table:

 

This achieves two-way one-to-multiple. One author can include multiple books and one book can contain multiple authors. This forms many to many.

Let's look at the implementation of the Code:

First, create a data table:

From sqlalchemy import Table, Column, Integer, String, DATE, ForeignKeyfrom sqlalchemy. orm import relationshipfrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import create_engineengine = create_engine ("mysql + pymysql: // root: root@192.168.123.207/xumingdb", encoding = 'utf-8') Base = declarative_base () base = declarative_base () book_m2m_author = Table ('book _ m2m_author ', Base. metadata, Column ('book _ id', Integer, ForeignKey ('books. id'), Column ('author _ id', Integer, ForeignKey ('authors. id'),) class Book (Base): _ tablename _ = 'books 'id = Column (Integer, primary_key = True) name = Column (String (64 )) pub_date = Column (DATE) authors = relationship ('author', secondary = book_m2m_author, backref = 'books ') def _ repr _ (self): return self. nameclass Author (Base): _ tablename _ = 'author' id = Column (Integer, primary_key = True) name = Column (String (32 )) def _ repr _ (self): return self. nameBase. metadata. create_all (engine) # create a table structure

Here we use another way to create a table and create the third table book_m2m_auther. After this table is created, we basically do not need to manually add data, for users, it is automatically maintained by the orm without worrying about the data. You do not need to create a ing relationship for it.

However, the mysql end is actually associated, because the foreign key has been set up. During orm query, we also need to map An orm-level memory object: relationship, tells the book table and author table which table to query data.

So look at the red code above and use the secondary field to check the third table.

At this time, we have established many-to-many relationships. We can insert data to see the results: (create the table first)

from day12 import sqlalchemy_multitomultifrom sqlalchemy.orm import sessionmakerSession_class=sessionmaker(bind=sqlalchemy_multitomulti.engine)session = Session_class()b1 = sqlalchemy_multitomulti.Book(name="book1",pub_date="2014-05-16")b2 = sqlalchemy_multitomulti.Book(name="book2",pub_date="2012-03-16")b3 = sqlalchemy_multitomulti.Book(name="book3",pub_date="2016-06-16")a1 = sqlalchemy_multitomulti.Author(name="xiaoming")a2 = sqlalchemy_multitomulti.Author(name="jack") a3 = sqlalchemy_multitomulti.Author(name="Rain")b1.authors = [a1,a3]b2.authors = [a2,a3]b3.authors = [a1,a2,a3]session.add_all([b1,b2,b3,a1,a2,a3])session.commit()

The red mark above indicates the association. After the association is established, the book_m2m_author table will automatically have data.

Of course, if we want to insert a Chinese book. That is, the inserted data has Chinese characters. How can we do this:

engine = create_engine("mysql+pymysql://root:root@192.168.123.207/xumingdb?charset=utf8",encoding='utf-8')Base = declarative_base()

What should I add after the database when creating a database connection? Charset = utf8

After the data is inserted, You can query the data:

1. Check the number of books published by xiaoming:

author_obj = session.query(sqlalchemy_multitomulti.Author).filter(sqlalchemy_multitomulti.Author.name=="xiaoming").first()print(author_obj.books)

 

2. view the authors of book b2:

book_obj = session.query(sqlalchemy_multitomulti.Book).filter(sqlalchemy_multitomulti.Book.id==2).first()print(book_obj.authors)

 

Multiple-to-multiple deletion:

When deleting data, we also do not need to worry about the book_m2m_author table. sqlalchemy will automatically help us Delete the corresponding data:

Delete the author from the book:

author_obj = session.query(sqlalchemy_multitomulti.Author).filter(sqlalchemy_multitomulti.Author.name=="xiaoming").first()book_obj = session.query(sqlalchemy_multitomulti.Book).filter(sqlalchemy_multitomulti.Book.id==2).first()book_obj.authors.remove(author_obj)session.commit()　

 

At this time, the association between book 2 is automatically missing.

Directly Delete the author:

author_obj = session.query(sqlalchemy_multitomulti.Author).filter(sqlalchemy_multitomulti.Author.name=="xiaoming").first()session.delete(author_obj)session.commit()

Summary

The above is a small series of two ways to use mysql in python, I hope to help you, if you have any questions, please leave a message, the small series will reply to you in a timely manner. Thank you very much for your support for the help House website!