pymysql and sqlalchemy in python learning

PyMySQL is a library used to connect to MySQL server in Python 3. X version,
MySQL DB is used in Python 2.


Database query operation

01 basic operation of Python database programming.py

import pymysql

#1) . create a database connection and autocommit whether to automatically submit changes to the database
conn = pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8')
#2) . create a cursor (used to execute sql statements)
cur = conn.cursor()

#3) . execute sql statement
insert_sql = 'insert into users(username) values ("user6");'
cur.execute(insert_sql)

print("Insert data successful.......")
#4) . close cursor
cur.close()
#5) . close connection
conn.close()

02 basic operation of Python database programming.py

class Connection(object):
  def __enter__(self):
        Context manager that returns a Cursor
        warnings.warn(
            "Context manager API of Connection object is deprecated; Use conn.begin()",
            DeprecationWarning)
        return self.cursor()

    def __exit__(self, exc, value, traceback):
        On successful exit, commit. On exception, rollback(RollBACK)
        if exc:
            self.rollback()
        else:
            self.commit()
import pymysql

#Using the with statement, pymysql.connect returns a database cursor. (see the source code for details)
with pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8') as cur:
    #3) . execute sql statement (add, delete and modify)
    insert_sql = 'insert into users(username) values ("user7");'
    cur.execute(insert_sql)
    print("Insert data successful.......")

03 database bulk operation.py

import pymysql

users = [('redhat' + str(i), 'passwd'+str(i)) for i in range(100)]
#users = [('redhat1', 'passwd1'), ('redhat2', 'passwd2'), .......]

#Using the with statement, pymysql.connect returns a database cursor. (see the source code for details)
with pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8') as cur:
    #3) . execute sql statement (add, delete and modify)
    insert_sql = 'insert into users(username, password) values (%s, %s);'
    #execute: executes an sql statement.
    #executemany: executes multiple sql statements.
    #'insert into users(username, password) values (%s, %s);' %users[0]
    #'insert into users(username, password) values (%s, %s);' %('redhat1', 'passwd1')
    #'insert into users(username, password) values (%s, %s);' %users[1]
    #'insert into users(username, password) values (%s, %s);' %users[2]
    cur.executemany(insert_sql, users)
    print("Insert data successful.......")

04 "Python query database information.py

import pymysql

with pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8') as cur:
    #3) . execute sql statement (add, delete and modify)
    query_sql = 'select * from users where username like "user%";'
    #Number of affected rows
    result = cur.execute(query_sql)
    print("Number of eligible records: ", result)

    #print(cur.fetchone())
    #print(cur.fetchmany(2))
    users_info = cur.fetchall()

#Beautiful printing in form.
from  prettytable import  PrettyTable
pt = PrettyTable(field_names=['number', 'User name', 'Password'])
for user in users_info:
    pt.add_row(user)
print(pt)

05 SQLite database.py

1. What is sqlite database?
SQLite is a lightweight, disk based file bag database management system (a file) embedded in Python,
It does not need to install and configure services, and supports using SQL statements to access the database. The database is developed in C language and supports most SQL91 standards,
It supports atomic, consistent, independent and persistent transactions, and does not support foreign key restrictions. It implements independent transactions through database level exclusive and shared locking,
When multiple threads access the same database at the same time and try to write data, only one thread can write data at each time.
2. Features of SQLite database:
SQLite supports a single database with a maximum size of 140TB. Each database is completely stored in a single disk file,
It is stored in the form of B + tree data structure, a database is a file, which can be backed up by directly copying the database file.
If you need to use visual management tools, you can download and use SQLiteManager, SQLite Database Browser, or other similar tools.
import sqlite3
 #1) . create connection
conn = sqlite3.connect(database='users.sqlite')

#2) . create cursor
cur = conn.cursor()

#3). Operation.
#if not exists create a table if the database mark does not exist, otherwise do not operate.
create_sql = 'create table if not exists userinfo1 (id int auto_increment primary key , name varchar(10) unique, passwd varchar(10) default "123");'
cur.execute(create_sql)
insert_sql = 'insert into userinfo1(name) values("user2");'
cur.execute(insert_sql)
#Data operations (addition, deletion and modification) must be submitted.
conn.commit()
select_sql = 'select * from userinfo1'
cur.execute(select_sql)
print("query result:", cur.fetchall())

cur.close()
conn.close()

What is ORM?
ORM, or object relational mapping, is used in relational databases and
There is a mapping between business entity objects, so that when we operate business objects, we do not need to
To deal with complex SQL statements, you only need to operate on the properties and methods of objects.

Install sqlalchemy

  1. Need a machine with mysql database installed
  2. Installation module: pip3 install sqlalchemy
  3. View version information
    Connect to database
    In sqlalchemy, session is used to create a session between a program and a database. Load of all objects
    The session object is required for entering and saving.

    Create map (create table)
    A mapping corresponds to a Python class that represents the structure of a table.

    Add single data

    Add multiple data

    Find data
    In the sqlalchemy module, finding data provides the method of query()

    Delete data

06 object relation mapping.py

import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
import pymysql
pymysql.install_as_MySQLdb()

#object(oop) ---> mysql(SQL)
#1) . create database engine (process of connecting to database)
#echo=True shows the translated SQL statement.
from sqlalchemy.orm import Session, sessionmaker

engine = sqlalchemy.create_engine("mysql://root:westos@localhost/Blog",
                         encoding='utf8', echo=True)

#2) . build cache
session = sessionmaker(bind=engine)()

#3) . base class to be inherited when creating database objects
Base = declarative_base()

#4) . create a class. A class is a database table
class Student(Base):
    # Name of the database table
    __tablename__ = 'student_info'
    #Property information of database table
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    sex = Column(String(10))

    #def __repr__(self):
    #return  'Student<%s>' %(self.name)

if __name__ == '__main__':
    # Create database table based on the information set - (create class: process of creating database table)
    Base.metadata.create_all(engine)

    #Add data information (instanced object: add a data / record)
    #INSERT INTO student_info (id, name, sex) VALUES (%(id)s, %(name)s, %(sex)s)
    stu1 = Student(id=2, name='westos2', sex='male')
    #stu2 = Student(id=3, name='westos3', sex ='male')
    #stu3 = Student(id=4, name='westos4', sex ='female')
    session.add_all([stu1, ])
    session.commit()

    #Query data information
    print(session.query(Student).all())

    #Query information according to filter criteria
    print(session.query(Student).filter_by(name='westos1').all())
    print(session.query(Student).filter_by(name='westos1').first())
    print(session.query(Student).filter_by(name='westoshdhwhdewd').first())

    #Delete data
    stu1 = session.query(Student).filter_by(name='westos2').first()

    print("before delete: ", stu1)
    session.delete(stu1)
    session.commit()
    stu1 = session.query(Student).filter_by(name='westos1').first()
    print("after delete: ", stu1)

Tags: Python Database Session SQL

Posted on Fri, 10 Jan 2020 05:13:03 -0500 by mhewall