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