编程知识 cdmana.com

Mysql database operation query record

Python Can be connected 、 Using a database .MySQL Is one of the most popular databases , This tutorial introduces the Python Middle link 、 Use MySQL database .
Insert multiple rows

To insert multiple rows into a table , Use executemany() Method .
executemany() The second argument to the method is a list of tuples , Contains the data to be inserted :

import mysql.connectormydb = mysql.connector.connect( host="localhost", user=" Your username ", passwd=" Your password ", database="mydatabase")mycursor = mydb.cursor()sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"val = [ ('Peter', 'Lowstreet 4'), ('Amy', 'Apple st 652'), ('Hannah', 'Mountain 21'), ('Michael', 'Valley 345'), ('Sandy', 'Ocean blvd 2'), ('Betty', 'Green Grass 1'), ('Richard', 'Sky st 331'), ('Susan', 'One way 98'), ('Vicky', 'Yellow Garden 2'), ('Ben', 'Park Lane 38'), ('William', 'Central st 954'), ('Chuck', 'Main Road 989'), ('Viola', 'Sideway 1633')]mycursor.executemany(sql, val)mydb.commit()print(mycursor.rowcount, "  The bar has been inserted ")
Gets the ID

By querying cursor object , Get the id.
Be careful : If you insert more than one line , Then returns the id.

import mysql.connectormydb = mysql.connector.connect( host="localhost", user=" Your username ", passwd=" Your password ", database="mydatabase")mycursor = mydb.cursor()sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"val = ("Michelle", "Blue Village")mycursor.execute(sql, val)mydb.commit()print("1  Record insert , ID:", mycursor.lastrowid)
prevent SQL Inject

When the user provides the query value , In order to prevent SQL Inject , These values should be escaped .

SQL Injection is a common web Hacker technology , Used to destroy or misuse a database .
mysql.connector The module has a way to escape the query value :
Example
Use placeholders %s Method to escape the query value :

import mysql.connectormydb = mysql.connector.connect( host="localhost", user=" Your username ", passwd=" Your password ", database="mydatabase")mycursor = mydb.cursor()sql = "SELECT * FROM customers WHERE address = %s"adr = ("Yellow Garden 2", )mycursor.execute(sql, adr)myresult = mycursor.fetchall()for x in myresult: print(x)
ORDER BY DESC

Use DESC keyword , You can sort the results in descending order .
Example
Press name Descending order of letters , Sort results :

import mysql.connectormydb = mysql.connector.connect( host="localhost", user=" Your username ", passwd=" Your password ", database="mydatabase")mycursor = mydb.cursor()sql = "SELECT * FROM customers ORDER BY name DESC"mycursor.execute(sql)myresult = mycursor.fetchall()for x in myresult: print(x)
Delete record

have access to “DELETE FROM” sentence , Delete a record from an existing table :
Example
Delete address as “Mountain 21” The record of :

import mysql.connectormydb = mysql.connector.connect( host="localhost", user=" Your username ", passwd=" Your password ", database="mydatabase")mycursor = mydb.cursor()sql = "DELETE FROM customers WHERE address = 'Mountain 21'"mycursor.execute(sql)mydb.commit()print(mycursor.rowcount, "  Records deleted ")

Be careful : After the database is modified , Need to use mydb.commit() Statement commit , No submission , The amendment will not take effect .
Be careful DELETE Statement WHERE Clause : WHERE Clause specifies which records should be deleted . If omitted WHERE Clause , All records will be deleted !

Delete only if the table exists

If the table to be deleted does not exist , Will report a mistake , have access to If EXISTS Keywords determine whether the table exists , Avoid reporting mistakes .
Example
Delete existing tables “customers”:

import mysql.connectormydb = mysql.connector.connect( host="localhost", user=" Your username ", passwd=" Your password ", database="mydatabase")mycursor = mydb.cursor()sql = "DROP TABLE IF EXISTS customers"mycursor.execute(sql)
Update table

have access to “UPDATE” sentence , Update the existing records in the form :
Example
Change the address bar from “Valley 345” to “Canyoun 123”:

版权声明
本文为[Irving the procedural ape]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225104629239x.html

Scroll to Top