编程知识 cdmana.com

Mysql database operation query record

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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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.connector

mydb = 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”:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user=" Your username ",
  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 have been updated ")

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

prevent SQL Inject

stay update In the sentence , In order to prevent SQL Inject , In general, the query value 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.connector

mydb = mysql.connector.connect(
  host="localhost",
  user=" Your username ",
  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 have been updated ")
Limit the number of results

have access to “LIMIT” sentence , Limit the number of records returned by the query :
Example
stay “customers” In the table , Choose the former 5 Bar record :

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user=" Your username ",
  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 go back , From 3 Records started 5 Bar record , have access to “OFFSET” keyword :
Example
From the position 3 Start , return 5 Bar record :

import mysql.connector

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

have access to JOIN sentence , According to the related fields between multiple tables , Combine these tables into one table .
Let's say I have a “users” Table and a “products” surface :

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 use the user's fav Fields and products id Field , Combine the two tables .
Example
Connect users Table and products surface , See what users like :

import mysql.connector

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

Be careful : JOIN And INNER JOIN Equivalent , They will give you the same result .

Left connection

In the example above ,Hannah and Michael Excluded from results , This is because INNER JOIN Show only matching records .
If you want to show all users , Even if they don't have a product they like , have access to LEFT JOIN sentence :

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"
The right connection

If you want to return all products , And users who like them , have access to RIGHT JOIN sentence :

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"

notes : Hannah And Michael, No favorite product , Excluded from results .

版权声明
本文为[WYH grass]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225110220286L.html

Scroll to Top