Python operation MySQL

1. Download Pymysql

PIP3 Install Pymysql

2. Import Module

>>> Import Pymysql

3. Create a connection

>>> conn = Pymysql.connect (host= "127.0.0.1", port=3306,user= "root", passwd= "123456", db= "test")

4. Creating Cursors

>>> cursor = conn.cursor ()

5. Execute SQL statement, note: The number of rows affected is returned

>>> Effect_row = Cursor.execute ("CREATE TABLE student (ID int not null,name varchar (10))")

6. Inserting data into the table

>>> Effect_row = Cursor.executemany ("INSERT into student (Id,name) VALUES (%s,%s)", [(1, "Wilson"), (2, "David")] )

>>> conn.commit ()//need to submit because the transaction is turned on by default

It's OK:

>>> date = [(5, "CC"), (6, "DD"), (7, "EE")]

>>> Effect_row = Cursor.executemany ("INSERT into student (Id,name) VALUES (%s,%s)", date)

>>> Conn.commit ()

7. Query data

>>> Effect_row = Cursor.execute ("SELECT * FROM Student")

>>> print (Cursor.fetchone ())//fetchone () reads only one

>>> print (Cursor.fetchall ())//fetchall () reads all the remaining

>>> Print (Cursor.fetchmany (3))//detchmany (n) get top N data

The above code is to write some native SQL statements, code duplication can not be resolved, the next sqlalchemy is to solve the problem of the solution, do not need to write the native SQL statements, and call the class method as the operation of the database.

1, Installation SQLAlchemy

PIP3 Install SQLAlchemy

2. CREATE TABLE structure using SQLAlchemy

Import SQLAlchemy

From SQLAlchemy import Create_engine

From sqlalchemy.ext.declarative import declarative_base

From SQLAlchemy import column,integer,string

From Sqlalchemy.orm import Sessionmaker

#创建表结构

Engine = Create_engine ("Mysql+pymysql://root:[email protected]/test", encoding= "Utf-8", echo=true)//Connect to database, root for username, 123456 for password, localhost for native login, test for library

Base = Declarative_base ()//Generate base class

Class User (Base):

__tablename__ = "User"//table name

id = Column (integer,primary_key=true)//Set ID field and primary key

Name = Column (String (32))//Set Name field

Password = Column (String (64))//Set Password field

Base.metadata.create_all (engine)//CREATE TABLE structure

3. Inserting data using SQLAlchemy

Session_class = Sessionmaker (bind=engine) #创建与数据库的会话session class, note that this is a class that is returned to the session, not an instance

Session = Session_class () #生成session实例

User_obj = User (name= "David", password= "123456") #生成你要创建的数据对象

Print (user_obj.name,user_obj.id) #此时还没创建对象呢, printing the ID is None

Session.add (User_obj) #把要创建的数据对象添加到这个session里, a unified creation

Print (user_obj.name,user_obj.id) #此时也依然还没创建

Session.commit () #现此才统一提交, creating data

Print (User_obj.name,user_obj.id,user_obj.password)

4. Enquiry

data = Session.query (User). Filter_by (Name= "David"). All () #first () take the first

Data2 = Session.query (User). Filter (user.id>1). All ()

Print (data)

Print (Data[0].name)

Print (DATA2)

data = Session.query (User). Filter (user.id>1). Filter (USER.ID<3). All () #多条件查询时, need to add filter

Print (data)

5. Modification

data = Session.query (User). Filter (user.id>1). Filter (USER.ID<3). First ()

Data.name = "Wilson"

Data.password = "Www.mc.com"

Session.commit ()

6. Rollback

Make_user = User (name= "Licis", password= "www")

Session.add (Make_user)

Print (Session.query (User). Filter (User.name.in_ (["Licis", "David"]). All ())

Session.rollback ()

Session.commit ()

7. Group statistics

From SQLAlchemy import func

Print (Session.query (Func.count (user.name), User.Name). group_by (User.Name). All ())

Python operation MySQL