python-mysql

python-mysql

创建数据库连接

import mysql.connector
mydb = mysql.connector.connect(
host='localhost',
db='scrapymysql',
user='root',
password='******',
)

---

创建数据库

mycursor = mydb.cursor()
mycursor.execute('CREATE DATABASE database_name)

---

创建数据表

mycursor = mydb.cursor()
mycursor.execute('CREATE TABLE table_name(name VARCHAR(255),url VARCHAR(255),)')

注：实际使用中，我们只需调用import pymysql
pymysql.connect()

---

显示数据库列表：

mycursor.execute('SHOW DATABASE')
for i in mycursor:
   print(i)

---

插入单数据：

sql = 'INSERT INTO table_name (name,url)VALUES (%s,%s)'
val = ('','')
mycursor.execute(sql,val)
mydb.commit()

---

插入多组数据

mycursor = mydb.cursor()
sql = 'INSERT INTO table_name (name,url) VALUES (%s,%s)'
val = [
('',''),
('','')
]
mycursor.executemany(sql,val)
mydb.commit()

---

添加主键id
mycursor.execute('ALTER TABLE website ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY')

---

查找数据

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM website")
myresult = mycursor.fetchall()#fetchall()获取所有记录

注：容易忽略execute中的“”
fetchone()读取一条数据
选取特定数据：
mycursor.execute(SELECT name,url FROM website)
where语句：
mycursor.execute(SELECT * FROM website where name=’’)
指定数据

---

排序
mycursor.execute(“SELECT * FROM website ORDER BY name ASCS/DESC”)