python 获取数据库数据展示 python读取数据库表

python连接mysql读取数据库详解

# 连接数据库
conn = pymysql.connect(host = '127.0.0.1' # 连接名称，默认127.0.0.1
, user='root'#用户名
, passwd='password' # 密码
, port=3306 # 端口，默认为3306
, db='pku' # 数据库名称
, charset='utf8' # 字符编码
)
cur = conn.cursor() # 生成游标对象
sql = "select * from pku.test limitit 100 " # SQL语句
cur.execute(sql) # 执行SQL语句
data = cur.fetchall() # 通过fetchall方法获得数据
dataSet = pd.DataFrame(data)
dataSet.columns = DataFrame(cur.description).iloc[:,0]

review = dataSet.iloc[:,0]
print(dataSet.head)

y = dataSet['REVIEW1']
print(y)

import requests
import pymysql
import mysql.connector
import numpy as np
import matplotlib.pyplot as plt
from matplotlib.collections import LineCollection
from sklearn import cluster, covariance, manifold



# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='password', db='mysql', port=3306)
cursor = conn.cursor()
cursor.execute("select * from thou0819")
cursor.execute("SHOW DATABASES")
for x in cursor:
  print(x)


mydb = pymysql.connect(host='localhost', user='root', passwd='password', database="mysql")
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE if not exists customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
# mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "条记录已插入")

# 这两个val说明，pymysql在插入新的记录时候，""和''没有区别。

sq = "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, " 条已经插入")


# 插入一行，并返回ID:
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Michelle", "Blue Village")
mycursor.execute(sql, val)
mydb.commit()
print("1 条记录插入, ID:", mycursor.lastrowid)


# 从MySQL表中选取(SELECT)数据，使用“SELECT”语句,从“customers”表中选取(SELECT)所有记录，并显示结果:
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)
# 我们使用了fetchall()方法，它从最后所执行语句的结果中，获取所有行。

# 如要选取表中的部分字段，使用“SELECT 字段1, 字段2 …”语句，选择name和address字段:
mycursor.execute("SELECT name, address FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

# 使用fetchone()方法，如果只想获取一行记录，可以使用fetchone()方法。fetchone()方法将返回结果的第一行:
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchone()
print(myresult)

# 从表中选取记录时，可以使用“WHERE”语句筛选, 选取地址为“Park Lane 38”的记录:
sql = "SELECT * FROM customers WHERE address ='Park Lane 38'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

# WHERE语句中可以使用通配符%。选取地址中包含单词“way”的记录
sql = "SELECT * FROM customers WHERE address LIKE '%way%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

是一种常见的w，用于破坏或误用数据库。
# mysql.connector 模块有方法可以转义查询值,使用占位符%s方法转义查询值:
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语句，按升序或降序对结果排序。
# 默认情况下，ORDER BY关键字按升序排列结果。要按降序排列，可使用DESC关键字。按name的字母顺序排列结果:
sql = "SELECT * FROM customers ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

# sql = "SELECT * FROM customers ORDER BY name DESC"


# 可以使用“DELETE FROM”语句，从现有表中删除记录，删除地址为“Mountain 21”的记录:
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 条记录删除")
# 注意： 数据库修改后，需要使用mydb.commit()语句提交，不提交，修改不会生效。
# 注意DELETE语句中的WHERE子句: WHERE子句指定应该删除哪些记录。如果省略WHERE子句，将删除所有记录!


# 可以使用“DROP table”语句，删除现有的表，删除表“customers”:
# sql = "DROP TABLE customers"
# sql = "DROP TABLE IF EXISTS customers"
# mycursor.execute(sql)


# 在“customers”表中，选择前5条记录:
mycursor.execute("SELECT * FROM customers LIMIT 5")
# pymysql.err.ProgrammingError: (1146, "Table 'mysql.customers' doesn't exist")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)
# 如果想返回，从第3条记录开始的5条记录，可以使用“OFFSET”关键字:
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

"""
数据库中join的inner join, outer join, cross join
1.以A，B两张表为例
A left join B
选出A的所有记录，B表中没有的以null 代替
right join 同理

2.inner join
A,B的所有记录都选出，没有的记录以null代替

3.cross join (笛卡尔积)
A中的每一条记录和B中的每一条记录生成一条记录
例如A中有4条，B中有4条，cross join 就有16条记录

1.事务四大特性
原子性，要么执行，要么不执行
隔离性，所有操作全部执行完以前其它会话不能看到过程
一致性，事务前后，数据总额一致
持久性，一旦事务提交，对数据的改变就是永久的
"""
mycursor.execute("CREATE TABLE if not exists users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), fav INT(30))")
sql = "INSERT INTO customers (id, name, fav) VALUES (%s, %s)"
val = [
  ('1',  'John', '154'),
  ('2', 'Peter', '154'),
  ('3', 'Amy', '155'),
  ('4', 'Hannah'),
  ('5', 'Michael'),
]
mycursor.executemany(sql, val)
# TypeError: not all arguments converted during string formatting
mydb.commit()
print(mycursor.rowcount, " 条已经插入")