Article catalog
learning from python web development from introduction to mastery
1. Install PyMySQL
Install pip install pymysql in conda virtual environment
2. Connection object
- Create a connected object
import pymysql
try:
connection = pymysql.connect(
host='localhost',
user='root',
password='123456',
db='michaeldata',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor # cursor type
)
print("Successfully connected:", connection)
except Exception as e:
print("Connection failed:", e)Output: connection succeeded: < pymysql connections. Connection object at 0x00000205AC8E96D0>
After successful connection, there are many methods to obtain the connection object. The common methods are as follows:
- cursor() gets the cursor object and operates the database
- commit() commit transaction
- rollback() rolls back the transaction
- close() closes the database connection
3. Cursor object
- cursor = connection.cursor()
Common methods for cursor objects:
- execute(operation, [, param]) executes database operations and SQL statements
- Execute any (operation, parameter sequence) performs operations in batches
- fetchone() gets the next item in the query result set
- fetchmany(size) gets the specified number of records
- fetchall() get all records
- close() closes the cursor
Operation process example:
import pymysql
try:
connection = pymysql.connect(
host='localhost',
user='root',
password='123456',
db='michaeldata',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor # cursor type
)
print("Successfully connected:", connection)
except Exception as e:
print("Connection failed:", e)
# sql statement
sql = '''
create table books(
id int not null auto_increment,
name varchar(255) not null,
category varchar(50) not null,
price decimal(10, 2) default '0',
publish_time date default null,
primary key (id)
) engine = InnoDB auto_increment=1
default charset = utf8mb4 collate = utf8mb4_0900_ai_ci;
'''
cursor = connection.cursor() # Get cursor object
cursor.execute(sql) # Execute sql statement
cursor.close() # Close the cursor first
connection.close() # Close the connection again, or use with as
4. Add, delete and modify
- For addition, deletion and modification, use cursor After execute() executes the SQL statement, it will not be submitted automatically by default. Instead, use connection Commit
- The insert statement uses% s as a placeholder to prevent SQL injection
cursor.execute(sql)
import pymysql
try:
connection = pymysql.connect(
host='localhost',
user='root',
password='123456',
db='michaeldata',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor # cursor type
)
print("Successfully connected:", connection)
except Exception as e:
print("Connection failed:", e)
# sql statement
sql = '''
create table if not exists books(
id int not null auto_increment,
name varchar(255) not null,
category varchar(50) not null,
price decimal(10, 2) default '0',
publish_time date default null,
primary key (id)
) engine = InnoDB auto_increment=1
default charset = utf8mb4 collate = utf8mb4_0900_ai_ci;
'''
cursor = connection.cursor() # Get cursor object
cursor.execute(sql) # Execute sql statement
sql1 = 'insert into books(name, category, price, publish_time) values("python web development", "python", "98.8", "2020-01-01")'
cursor.execute(sql1) # Execute sql statement
connection.commit() # connection must be submitted to take effect
cursor.close() # Close the cursor first
connection.close() # Close the connection again, or use with as
cursor.executemany(sql, seq_of_params)
Batch operation
import pymysql
try:
connection = pymysql.connect(
host='localhost',
user='root',
password='123456',
db='michaeldata',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor # cursor type
)
print("Successfully connected:", connection)
except Exception as e:
print("Connection failed:", e)
# sql statement
sql = '''
create table if not exists books(
id int not null auto_increment,
name varchar(255) not null,
category varchar(50) not null,
price decimal(10, 2) default '0',
publish_time date default null,
primary key (id)
) engine = InnoDB auto_increment=1
default charset = utf8mb4 collate = utf8mb4_0900_ai_ci;
'''
cursor = connection.cursor() # Get cursor object
cursor.execute(sql) # Execute sql statement
# sql1 = 'insert into books(name, category, price, publish_time) values("python web development", "python", "98.8", "2020-01-01")'
# cursor.execute(sql1) # Execute sql statement
# connection.commit() # connection must be submitted to take effect
# Data list
data = [("Zero Basics Python", 'Python', '79.80', '2018-5-20'),
("Python From introduction to mastery", 'Python', '69.80', '2018-6-18'),
("Zero Basics PHP", 'PHP', '69.80', '2017-5-21'),
("PHP Introduction to project development", 'PHP', '79.80', '2016-5-21'),
("Zero Basics Java", 'Java', '69.80', '2017-5-21'),
]
try:
cursor.executemany('insert into books(name, category, price, publish_time) values(%s, %s, %s, %s)', data)
connection.commit() # connection must be submitted to take effect
except Exception as e:
connection.rollback() # RollBACK
cursor.close() # Close the cursor first
connection.close() # Close the connection again, or use with as
5. Query operation
- Execute the select query to generate the result set, and then use the fetchone/fetchmany/fetchall () related statements to get the records
import pymysql
try:
connection = pymysql.connect(
host='localhost',
user='root',
password='123456',
db='michaeldata',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor # cursor type
)
print("Successfully connected:", connection)
except Exception as e:
print("Connection failed:", e)
# sql statement
sql = "select * from books order by price"
with connection.cursor() as cursor:
cursor.execute(sql) # Execute sql statement
result1 = cursor.fetchone() # Get query results
result2 = cursor.fetchall() # Get query results
print(result1)
print("*" * 10)
for res in result2:
print(res)
connection.close() # Close connectionOutput results:
Successfully connected: <pymysql.connections.Connection object at 0x00000216C72696D0>
{'id': 5, 'name': 'Python From introduction to mastery', 'category': 'Python', 'price': Decimal('69.80'), 'publish_time': datetime.date(2018, 6, 18)}
**********
{'id': 6, 'name': 'Zero Basics PHP', 'category': 'PHP', 'price': Decimal('69.80'), 'publish_time': datetime.date(2017, 5, 21)}
{'id': 8, 'name': 'Zero Basics Java', 'category': 'Java', 'price': Decimal('69.80'), 'publish_time': datetime.date(2017, 5, 21)}
{'id': 4, 'name': 'Zero Basics Python', 'category': 'Python', 'price': Decimal('79.80'), 'publish_time': datetime.date(2018, 5, 20)}
{'id': 7, 'name': 'PHP Introduction to project development', 'category': 'PHP', 'price': Decimal('79.80'), 'publish_time': datetime.date(2016, 5, 21)}
{'id': 3, 'name': 'python web development', 'category': 'python', 'price': Decimal('98.80'), 'publish_time': datetime.date(2020, 1, 1)}6. ORM programming
ORM Object Relational Mapping
It maps the database to objects
- table - class
- record - object
- field - attribute
ORM example writing method: data = book query. all()
Benefits:
- The data model facilitates code reuse
- There are many ready-made tools to complete preprocessing, things, etc
- The business code based on ORM is simple, semantic and easy to understand
- You don't have to write bad sql
Disadvantages:
- ORM library is not a lightweight tool with high learning cost
- Complex queries that cannot be expressed or perform worse than native SQL
- ORM abstracts the database layer and cannot understand the underlying operations, so it cannot customize special SQL
Common python ORM Libraries
- Django ORM is closely integrated with Django
- SQLAlchemy is mature
- Peewee is lightweight and developed based on SQLAlchemy
- Storm medium, allowing cross database queries