关于python:Python入门系列十一一篇搞定python操作MySQL数据库

开始

装置MySQL驱动

$ python -m pip install mysql-connector-python

测试MySQL连接器

import mysql.connector

测试MySQL连贯

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword")print(mydb)

创立数据库

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword")mycursor = mydb.cursor()mycursor.execute("CREATE DATABASE mydatabase")

创建表格

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

插入数据

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"val = ("John", "Highway 21")mycursor.execute(sql, val)mydb.commit()print(mycursor.rowcount, "record inserted.")

重要！。留神这个语句：mydb.commit()。它是进行批改的必要条件，否则就不会对表进行批改。

插入多行

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  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, "was inserted.")

获取插入的ID

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  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 inserted, ID:", mycursor.lastrowid)

查问

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()mycursor.execute("SELECT * FROM customers")myresult = mycursor.fetchall()for x in myresult:  print(x)

从表格中抉择

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()mycursor.execute("SELECT * FROM customers")myresult = mycursor.fetchall()for x in myresult:  print(x)

留神：咱们应用fetchall()办法，它从最初执行的语句中获取所有行。

抉择列

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()mycursor.execute("SELECT name, address FROM customers")myresult = mycursor.fetchall()for x in myresult:  print(x)

如果只想查问一条数据，能够应用fetchone()办法。

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()mycursor.execute("SELECT * FROM customers")myresult = mycursor.fetchone()print(myresult)

Where条件

应用筛选器抉择

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()sql = "SELECT * FROM customers WHERE address ='Park Lane 38'"mycursor.execute(sql)myresult = mycursor.fetchall()for x in myresult:  print(x)

通配符

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()sql = "SELECT * FROM customers WHERE address LIKE '%way%'"mycursor.execute(sql)myresult = mycursor.fetchall()for x in myresult:  print(x)

避免SQL注入

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  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

对后果进行排序

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

应用DESC关键字对后果进行降序排序。

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  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)

删除记录

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

避免SQL注入

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()sql = "DELETE FROM customers WHERE address = %s"adr = ("Yellow Garden 2", )mycursor.execute(sql, adr)mydb.commit()print(mycursor.rowcount, "record(s) deleted")

删除表

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()sql = "DROP TABLE customers"mycursor.execute(sql)

更新数据

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"mycursor.execute(sql)mydb.commit()print(mycursor.rowcount, "record(s) affected")

避免SQL注入

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  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, "record(s) affected")

限度后果

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()mycursor.execute("SELECT * FROM customers LIMIT 5")myresult = mycursor.fetchall()for x in myresult:  print(x)

从另一个地位开始

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")mycursor = mydb.cursor()mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")myresult = mycursor.fetchall()for x in myresult:  print(x)

连贯两张或更多数据表

# 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: 154, name: 'Chocolate Heaven' },{ id: 155, name: 'Tasty Lemons' },{ id: 156, name: 'Vanilla Dreams' }

import mysql.connectormydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  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)

完结

MySQL的基本操作就是这样子了。