import pymysql
import pandas as pd
db=pymysql.connect(host='localhost',
port=3306,
user='root',
password='root',
db='test',
charset='utf8'
)
sql='select * from memberinfo'
df=pd.read_sql(sql,db) #連線MySQL資料庫，讀取表
"""
#將DF寫入MySQL資料庫，建立一個新表
from sqlalchemy import create_engine #用DF建立MySQL表
engine = create_engine("mysql+pymysql://root:[email protected]:3306/test",encoding='gbk')
df.to_sql('memberinfo2', con=engine)
#df.to_sql(name= 'memberinfo2',con=engine,if_exists='replace',index=False,index_label=False)
sql2='select * from memberinfo2'
df2=pd.read_sql(sql2,db)
print(df2)
"""
#利用cursor.execute操縱資料庫
cursor=db.cursor()
cursor.execute("SELECT VERSION()")
cursor.execute(sql) #執行SQL命令
data=cursor.fetchall() #獲取資料
print(data)
print()
"""
sql="insert into memberinfo values('A2',16,null,'2018-08-09')" #新增記錄
sql="desc memberinfo" #顯示錶結構
sql="alter table memberinfo modify column 性別 varchar(4)" #修改表結構
sql="update memberinfo set 年齡=44 where 性別='女'" #修改資料
cursor.execute(sql)
"""
#利用cursor.execute建立資料表
cursor.execute("DROP TABLE IF EXISTS student")
sql2='''
CREATE TABLE student(
S VARCHAR(10),
Sname VARCHAR(10),
Sage DATE,
Ssex VARCHAR(10)
);'''
sql='''
INSERT INTO student (S,Sname, Sage, Ssex) VALUES
('01','frank1','1991-01-01','Male'),
('02','frank2','1992-07-07','Female'),
('03','frank3','1993-07-07','Male'),
('04','frank4','1994-07-07','Male');
'''
try:
cursor.execute(sql)
db.commit()
print('commit')
except:
db.rollback()
sql="select * from student"
cursor.execute(sql)
df=pd.DataFrame()
df=cursor.fetchall() #利用dataframe分行輸出記錄
for i in df:
print(i)
"""
#利用cursor.execute按條件查詢
cursor=db.cursor()
sql="select * from student where DATE_FORMAT(Sage,'%Y-%m-%d')<'2018-03-21'"
try:
# 執行SQL語句
cursor.execute(sql)
# 獲取所有記錄列表
results = cursor.fetchall()
for row in results:
S = row[0]
Sname = row[1]
Sage = row[2]
Ssex = row[3]
# 列印結果
print(S, Sname, Sage,Ssex)
except:
print( "Error: unable to fecth data")
"""
db.close()