This article explains the database operation in python in detail

Database operation in python

Recently, after setting up, you need to add a database and record it.

1, Database programming interface

It focuses on the connection object and cursor object in the database API interface

1. Connection object

The database connection object mainly provides methods to obtain the database cursor object, commit / rollback transactions, and close the database connection.

Use the connect function to get the object, which has multiple parameters:

  • host hostname
  • database/db database name
  • User user name
  • Password user password
  • charset encoding method

Use PyMySQL module to connect to MySQL database.

conn = pymysql.connect(host='localhost',user='user',password='passwd',db='test',charset='utf-8')

The connect function returns a connection object that represents the current session with the database.

The methods supported by the connection object are as follows:

  • close() close the database connection
  • commit() commit transaction
  • rollback() rollback transaction
  • cursor() gets cursor objects and operates the database, such as executing DML operations and calling stored procedures

For the commit method here, commit is used to commit transactions, which mainly deal with data with large amount and high complexity. If the operation is a series of actions, such as Zhang San transferring money to Li Si: then Zhang San's account balance decreases and Li Si's account balance increases, then using transactions can maintain the integrity of the database, that is, to ensure that the two operations are either fully executed or not executed.

For cursor objects, they represent cursors in the database and are used to indicate the context of data fetching operations. They mainly provide methods such as executing SQL statements, calling stored procedures, and obtaining query results.

The cursor method of the connection object can be used to obtain the cursor object. The attributes of the cursor object are as follows:

  • Description attribute: indicates the database column type and the value description information

  • rowcount attribute: returns the row count statistics of the result, such as SELECT, UPDATE, and CALLPROC

  • callproc(procname[,parameters]) calls stored procedures and requires database support

  • close() closes the current cursor

  • execute(operation[,parameters]) executes database operations, SQL statements or database commands

2, Use built-in SQLite

SQLite is an embedded database, which is itself a file. SQLite stores the entire database (including tables, indexes, and data itself) in the host as a single file that can be used across platforms. Because SQLite itself is developed in C language and is small in size, it is often integrated into various applications. SQLite is built in Python, so using SQLite database in Python does not need to install any modules, and it can be used directly.

1. Create database file

Create SQLite database file:

import sqlite3

# Connect to SQLite database
# The database file is mrsoft DB if the file does not exist, it will be automatically created in the current directory

conn = sqlite3.connect('mrsoft.db')

# Create a cursor
cursor = conn.cursor()

# Use cursor object to execute SQL statement and create user table
cursor.execute('create table user (id int(10) primary key, name varchar(20))')

# Close cursor
cursor.close()

# Close Connection
conn.close()

In the above code, the sqlite3.connect() method is used to connect the SQLite database file mrsoft.db. Since the mrsoft.db file does not exist, the mrsoft.db file will be created in the same level directory of the python code of this example, which contains the relevant information of the user table.

But if you execute the above code again, you will be prompted with an error message because the user table already exists.

2. Operate SQLite

To add data to the data table, you can use the insert statement in SQL.

The syntax is as follows:

**insert into Table name (field name 1, field name 2, field name 3) values(Field value 1, field value 2,...field value n)**

In the user table created above, there are two fields named ID and name. The field value needs to be assigned according to the type of field.

Then execute the SQL statement:

cursor.execute('insert into user(id,name) values(1,"MRSOFT")')
cursor.execute('insert into user(id,name) values(2,"Andy")')
cursor.execute('insert into user(id,name) values(3,"SiRi")')

Case 1: insert three records into the database

import sqlite3

# Connect to SQLite database
# The database file is mrsoft DB if the file does not exist, it will be automatically created in the current directory

conn = sqlite3.connect('mrsoft.db')

# Create a cursor
cursor = conn.cursor()

# Use cursor object to execute SQL statement and create user table
# cursor.execute('create table user (id int(10) primary key, name varchar(20))')

# Execute insert statement single outside quotation mark and double inside quotation mark
cursor.execute('insert into user(id,name) values(1,"MRSOFT")')
cursor.execute('insert into user(id,name) values(2,"Andy")')
cursor.execute('insert into user(id,name) values(3,"Siri")')

# Close cursor
cursor.close()

# Commit transactions commit transactions using database connection objects
conn.commit()

# Close Connection
conn.close()

Case 2: viewing user information

To find the data in the data table, you can use the select statement in SQL:

select Field name 1, field name 2, field name 3,  from Table name where query criteria

This is the data set that meets the query conditions, but we may only need some data, so we also need specific query statements.

  • fetchone(): get the next record in the query result set

  • fetchmany(size): get a specified number of records

  • fetchall(): get all records of the structure set

    import sqlite3
    con = sqlite3.connect('mrsoft.db ') # connect to the database

    Create a cursor to get the cursor object

    cursor = con.cursor()

    Execute query statement

    cursor.execute('select * from user')

    Get the query result, get the first record

    fetchone returns a tuple

    result1 = cursor.fetchone()
    print(result1)

    Close cursor

    cursor.close()

    Close connection

    con.close()

    import sqlite3
    con = sqlite3.connect('mrsoft.db ') # connect to the database

    Create a cursor to get the cursor object

    cursor = con.cursor()

    Execute query statement

    cursor.execute('select * from user')

    Get the query result, get the first record

    fetchone returns a tuple

    result1 = cursor.fetchone()

    print(result1)

    Using fetchmany to query multiple pieces of data

    Returns a list with two tuples

    result2 = cursor.fetchmany(2)

    print(result2)

    result3 = cursor.fetchall() # get all data
    print(result3)

    Close cursor

    cursor.close()

    Close connection

    con.close()

    import sqlite3
    con = sqlite3.connect('mrsoft.db ') # connect to the database

    Create a cursor to get the cursor object

    cursor = con.cursor()

    Execute the query statement to query all tuples with ID > 1

    cursor.execute('select * from user where id > 1')

    Get the query result, get the first record

    fetchone returns a tuple

    result1 = cursor.fetchone()

    print(result1)

    Using fetchmany to query multiple pieces of data

    Returns a list with two tuples

    result2 = cursor.fetchmany(2)

    print(result2)

    result3 = cursor.fetchall() # get all data
    print(result3)

    Close cursor

    cursor.close()

    Close connection

    con.close()

Case 3: modifying user data information

The syntax is as follows:

update Table name set Field name = field value where query criteria


import sqlite3

# Connect to the database
con = sqlite3.connect("mrsoft.db")

# Create cursor object
cursor = con.cursor()

# Perform database update operation
cursor.execute('update user set name = "xiaxuefei" where id = 1')

cursor.execute('select * from user')

result = cursor.fetchall()
print(result)

cursor.close()

con.commit()  # Commit transaction

con.close()

Case 4: deleting user information

The delete statement in SQL can be used to delete the data in the database table. The syntax is as follows:

delete from Table name where query criteria


import sqlite3

# Connect to the database
con = sqlite3.connect('mrsoft.db')

# Create a cursor object
cursor = con.cursor()

# Delete user with ID 1
cursor.execute('delete from user where id = 1')

# Get all user information
cursor.execute('select * from user')

# Record query results
result = cursor.fetchall()
print(result)

# Close cursor
cursor.close()

# Commit transaction
con.commit()

# Close connection
con.close()

3, Use of MySql database

3.1 install MySql

Enter the select setting type interface. Five types:

  • Developer Default: tools required for installing MySql server and developing MySql applications, including GUI platform for developing and managing server, EXCEL plug-ins for accessing operation data, plug-ins developed with Visual Studio integration, connectors for accessing data through NET/Java/C/C++/ODBC, official sample tutorials, and development documents.
  • Server only: only install MySql server, which is suitable for deploying MySql server
  • Client only: only install the client, which is applicable to MySQL application development based on the existing MySQL server
  • Full: install all available components of MySql
  • Custom: customize the required components

3.2 setting environment variables

After installation, the default path must be "C:Program FilesMySQL Server 8.0in". Create an environment variable and write the path.

3.3 start MySql

Use the following two commands:

net start mysql  # Start mysql server

mysql -u root -p # You need to enter a password to enter the MySQL server

Note that entering the above two commands requires administrator permission.

3.4 using navicat for mysql management software

Here I install the cracked version of navicat for mysql

You can customize the installation path:

Then use PatchNavicat.exe to crack. It can be used normally

Link: https://pan.baidu.com/s/1SeCf0BCIO1LHB5-tjPD0OQ?pwd=8x2u 
Extraction code: 8 x2u 
--From Baidu online disk super member V2 Share of

Then create a new connection:

Enter the connection name, host name or IP address, fill in: localhost or 127.0.0.1, and enter the login password of the MYsql database. Here is root

Next, use Navicat to create a Mrsoft database, right-click Mr - > select new database - > Enter database information.

3.5 installing PyMysql module

Because the MySQL server runs in an independent process and provides external services through the network, it needs a MySQL driver that supports Python to connect to the MySQL server. There are many modules that support MySQL database in Python. PyMySql is selected here.

The installation commands are as follows:

pip install PyMySQL

3.6 connecting to the database

import pymysql

# Open database connection parameter 1: database domain name or IP; Parameter 2: database account number; Parameter 3: database password parameter 4: database name
db = pymysql.connect(host="localhost",user="root",password="123",database="mrsoft")

# Create a cursor object
cursor = db.cursor()

# Execute SQL query
cursor.execute("SELECT VERSION()")

# Use fetchone() to get a single piece of data
data = cursor.fetchone()
print("DataBase version : %s " % data)

# Close cursor object connection
cursor.close()

# Close database connection
db.close()

The above code is only used to check the version of the database.

The running results are as follows: database version: 5.1.32-community

4, Create data table

Next, create the database table through execute().

4.1 create books table

The books table contains ID (primary key), name (book name), category (book classification), price (book price), and publish_ Time has five fields.

The sql statement is as follows:

CREATE TABLE books(
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    category varchar(50) NOT NULL,
    price decimal(10,2) DEFAULT NULL,
    publish_time date DEFAULT NULL,
    PRIMARY KEY(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Before creating a database table, test whether the table already exists:

drop table if exists books

It means that if the table books already exists in the mrsoft database, delete books first, and then create the books data table. The specific codes are as follows:

import pymysql
# Open database
db = pymysql.connect(host='localhost',user='root',password='123',database='mrsoft')

# Use cursor() method to execute SQL, and delete the table if it exists
cursor = db.cursor()

# Use execute to execute SQL, and delete the table if it exists
cursor.execute('DROP TABLE IF EXISTS books')

# Create a table using preprocessing statements
sql = """
CREATE TABLE books(
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    category varchar(50) NOT NULL,
    price decimal(10,2) DEFAULT NULL,
    publish_time date DEFAULT NULL,
    PRIMARY KEY(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
"""

# Execute SQL statement
cursor.execute(sql)
db.close()

4.2 operating MySql data tables

CRUD is the main operation of MySQL data table.

Case: batch adding book data:

To insert data, you can use execute() to add a record, or you can use the executemany() method to batch add multiple records. The executemany() syntax is as follows:

executemany(operation,seq_of_params)

Operation: SQL statement of operation
seq_of_params: parameter sequence

Use executemany() to batch add multiple records.

import pymysql
# Open database
db = pymysql.connect(host='localhost',user='root',password='123',database='mrsoft')

# Use cursor() method to execute SQL, and delete the table if it exists
cursor = db.cursor()

data = [("Zero basic learning python",'python','1',"2018-09-10"),("Zero basic learning java",'java','11',"2018-09-11")]

try:
    # Execute sql statement to insert multiple pieces of data. The second parameter is the data to be inserted
    cursor.executemany("insert into books(name,category,price,publish_time) values(%s,%s,%s,%s)",data)

    # Commit transaction
    db.commit()

except:
    # Rollback when an error occurs
    db.rollback()

db.close()
  • The connect() method connects to the database and sets charset=utf8 as an additional character set to prevent garbled codes when inserting Chinese.
  • When inserting data with Insert statement, using%s as placeholder can prevent SQL injection.

Tags: Front-end Android Back-end Interview

Posted by fitzsic on Fri, 12 Aug 2022 21:54:17 +0300