Mysql database operation query record

Time:2021-12-4
Insert multiple rows

To insert multiple rows into a table, use the executenamy () method.
The second parameter of the executenamy () method is a tuple list containing the data to be inserted:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  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, "bar inserted")
Gets the ID of the inserted row

You can obtain the ID of the row just inserted by querying the cursor object.
Note: if multiple rows are inserted, the ID of the last inserted row is returned.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  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 ("insert 1 record, ID:", myCursor. Lastroid ")
Prevent SQL injection

When users provide query values, they should be escaped to prevent SQL injection.

SQL injection is a common web hacker technology, which is used to destroy or misuse the database.
The mysql.connector module has methods to escape query values:
Example
Escape query value using placeholder% s method:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  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 the desc keyword to sort the results in descending order.
Example
Sort the results in descending alphabetical order of Name:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  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

You can use the delete from statement to delete records from an existing table:
Example
Delete the record with address “mountain 21”:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  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")

Note: after database modification, you need to use mydb. Commit() statement to submit. If you do not submit, the modification will not take effect.
Note the where clause in the delete statement: the where clause specifies which records should be deleted. If the where clause is omitted, all records will be deleted!

Delete only if table exists

If the table to be deleted does not exist, an error will be reported. You can use the if exists keyword to judge whether the table exists to avoid an error.
Example
Delete the existing table “customers”:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  Passwd = "your password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS customers"

mycursor.execute(sql)
Update table

You can use the “update” statement to update existing records in the table:
Example
Rewrite the address bar from “Valley 345” to “canyoung 123”:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  Passwd = "your password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"

mycursor.execute(sql)

mydb.commit()

Print (mycursor.rowcount, "records updated")

Note: after database modification, you need to use mydb. Commit() statement to submit. If you do not submit, the modification will not take effect.
Note the where clause in the update statement: the where clause specifies which records should be updated. If the where clause is omitted, all records will be updated!

Prevent SQL injection

In the update statement, in order to prevent SQL injection, you should usually escape the query value.
SQL injection is a common web hacker technology, which is used to destroy or misuse the database.
The mysql.connector module has methods to escape query values:
Example
Escape query value using placeholder% s method:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  Passwd = "your password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")

mycursor.execute(sql, val)

mydb.commit()

Print (mycursor.rowcount, "records updated")
Limit the number of results

You can use the “limit” statement to limit the number of records returned by the query:
Example
In the “customers” table, select the first 5 records:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  Passwd = "your password",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)
Start at the specified location

If you want to return the five records starting from the third record, you can use the “offset” keyword:
Example
Starting from position 3, return 5 records:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  Passwd = "your password",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)
Join two or more tables

You can use the join statement to combine multiple tables into one table according to the relevant fields between them.
Suppose you have a users table and a products table:

users

{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

products

{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

You can combine the two tables using the user’s fav field and the product’s ID field.
Example
Connect the users table and the products table to view the products that users like:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  User = "your user name",
  Passwd = "your password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Note: join is equivalent to inner join. They will give you the same result.

Left connection

In the above example, Hannah and Michael are excluded from the results because the inner join only displays matching records.
If you want to display all users, even if they don’t have a favorite product, you can use the left join statement:

Example
Query all users and their favorite products:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  LEFT JOIN products ON users.fav = products.id"
Right connection

If you want to return all products and users who like them, you can use the right join statement:

Example
Query all products and users who like them:

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  RIGHT JOIN products ON users.fav = products.id"

Note: Hannah and Michael have no favorite products and are excluded from the results.

Recommended Today

Hive built-in function summary

1. Related help operation functions View built-in functions: Show functions; Display function details: desc function ABS; Display function extension information: desc function extended concat; 2. Learn the ultimate mental method of built-in function Step 1: carefully read all the functions of the show functions command to establish an overall understanding and impression Step 2: use […]