1.5 PyCharm连接数据库

连接数据库

1.导入第三方库
as:别名

import pymysql as mysql #加载所有
from pymysql import connect #加载局部

2.conncet连接函数

host = "localhost"  #127.0.0.1=本机IP
user = "root"
password = "123456"
db = "mysql" #数据z库
port = 3306 #number类型
dbHelp=mysql.connect(host=host,user=user,password=password,db=db,port=port)
print("连接成功")

3.获取游标：执行、编译str类型SQL语句

cur=dbHelp.cursor()

4.创建表结构

cur.execute("CREATE TABLE IF NOT EXISTS user_info(ID INT PRIMARY KEY AUTO_INCREMENT"
            ",name VARCHAR(25))")
#1.插入:返回值，0，失败   >0:成功
i=cur.execute("INSERT INTO user_info(name) VALUES('Liyifeng')")
dbHelp.commit() #提交
print(i)

#2.修改:返回值，0，失败   >0:成功
#格式化字符串： %s：string %d:int
sql = "UPDATE user_info SET name='%s' WHERE id=%d"
i=cur.execute(sql%(("cxk",1)))
dbHelp.commit() #提交
print(i)

#3.查询 :返回值更新的条目，0，失败   >0:成功
sql = "SELECT *  FROM user_info  "
cur.execute(sql)
list = cur.fetchall() #提交
for item in list:
    print(item[0],item[1])
# print(i)

#4.删除 :返回值更新的条目，0，失败   >0:成功
sql = "DELETE *  FROM user_info WHERE id=%d "
cur.execute(sql)
dbHelp.commit()
# print(i)