Python uses the database

Source: Internet
Author: User

With PYTHON-DB-API, you only need to figure out the connection, cursor objects, you must close after opening.


The cursor object performs an INSERT, UPDATE, Delete, and the result is returned by the number of rows affected by rowcount, resulting in the execution.


The cursor object executes a SELECT, gets the result set of the list by Featchall (), each element is a tuple and corresponds to a row of records


SQL with parameters, you need to pass parameters to the Execute () method by location.


Install the official MySQL-provided Python driver

Pip Install Mysql-connection-python--allow-external Mysql-connection-python


Steps to operate MySQL:

Import Mysql.connector

Conn=mysql.connector.connect (user= ' root ', password= ' password ', database= ' test ')

Cursor=conn.cursor ()

Cursor.execute (' CREATE table user (ID varchar (primary key, name varchar (20)) ')

Cursor.execute (' INSERT into user (ID, name) values (%s,%s) ', [' 1 ', ' Michael '])

Cursor.rowcount


Conn.commit ()

Cursor.close ()


cursor = Conn.cursor ()

Cursor.execute (' select * from user where id =%s ', [' 1 '])

Values = Cursor.fetchall ()

Values


Cursor.close ()

Conn.close ()


ORM Technology--object-relational Mapping, maps the table acceptance of a relational database to an object.

ORM Framework--SQLAlchemy

SQLAlchemy Application, Initialize Dbsession

From SQLAlchemy import Column, String, create_enginefrom sqlalchemy.orm import Sessionmakerfrom        Sqlalchemy.ext.declarative Import declarative_basebase = Declarative_base () class User (base): __tablename__ = ' user ' id = column (string), Primary_key = True) name = Column (string) engine = Create_engine (' Mysql+mysqlcon Nector://root:[email protected]:3306/test ') dbsession = Sessionmaker (bind=engine)

A table corresponds to a class. Adding a record using ORM can be seen as adding a user object.

Session = Dbsession () new_user=user (id= ' 5 ', name= ' Bob ') Session.add (New_user) session.commit () Session.close ()

The key to adding data is to get the session, then add the object to the session, and finally commit and close. The Dbsession object can be considered the current database connection.

Use ORM to query data from a database: The result is a user object, not a tuple.

Session = Dbsession () user = Session.query (user). Filter (user.id== ' 5 '). One () print (' Type: ', type (user) print (' Name: ', User.Name) Session.close ()

Multiple tables implement a one-to-many, many-to-many association through foreign keys. The ORM framework can also implement one-to-many, multi-pair versatility.

Class User (Base): __tablename__ = ' user ' id = column (string), Primary_key = True) name = Column (String (20) ) Books = relationship (' book ') class book (Base): __tablename__ = ' book ' id = Column (String), PR imary_key=true) name = Column (string) user_id = Column (String), ForeignKey (' User,id ')

When querying a user object, the books property of the object returns a list containing several book objects.


The purpose of ORM is to convert a row of records from a database table to an object, using ORM as a prerequisite for understanding the principles of relational databases.
















Python uses the database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.