首先安装一下 pymysql:
py -3 -m pip install pymysql
py -3 -m pip show pymysql
统计一下多少人连数据库
netstat -anp|grep 3306|wx -l
linux上常用的查看日志命令:
root@iZ2zejbxp2btn9jh8knipuZ wxh]# tail -1 a.txt
ad
[root@iZ2zejbxp2btn9jh8knipuZ wxh]# tail -2 a.txt
ad
ad
[root@iZ2zejbxp2btn9jh8knipuZ wxh]# head -2 a.txt
ad
ad
[root@iZ2zejbxp2btn9jh8knipuZ wxh]# head -1 a.txt
ad
root@iZ2zejbxp2btn9jh8knipuZ ~]# echo "ad">>/home/wxh/a.txt
[root@iZ2zejbxp2btn9jh8knipuZ ~]# echo "ad">>/home/wxh/a.txt
连数据库第一次程序:
#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "root",
db = "test_db",
charset = "utf8")
#使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
print(cursor)
print(type(cursor))
第二次修改程序:创建数据库成功异常捕获
#coding=utf-8
import pymysql
try:
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "test_db"
)
cur = conn.cursor()
cur.execute('CREATE DATABASE IF NOT EXISTS grdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
cur.close()
conn.close()
print("创建数据库pythonDB成功! ")
except pymysql.Error as e:
print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
第3次修改,加入了建表语句:
#coding=utf-8
import pymysql
try:
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "root"
)
conn.select_db('grdb')# 选择pythonDB数据库
cur = conn.cursor()# 获取游标
# 如果所建表已存在,删除重建
cur.execute("drop table if exists User;")
# 执行建表sql语句
cur.execute('''CREATE TABLE `User`(
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL
)ENGINE=innodb DEFAULT CHARSET=utf8;''')
cur.close()
conn.close()
print(u"创建数据表成功")
except pymysql.Error as e:
print("Mysql Error %d: %s" %(e.args[0], e.args[1]))
倒数:
#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "gloryroad" ,
db = "grdb",
charset = "utf8"
)
#使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
#插入一条数据
insert = cursor.execute("insert into user values(1,'Tom','123','1990-01-01')")
print(u"添加语句受影响的行数:", insert)
#另一种插入数据方法,通过格式字符串传入值,此方式可以防止sql注入
sql = "insert into user values(%s, %s, %s, %s)"
insert = cursor.execute(sql, (3,'lucy','efg','1993-02-01'))
print(u"添加语句受影响的行数:", insert)
#关闭游标
cursor.close()
#提交事务
conn.commit()
#关闭数据库连接
conn.close()
print(u"sql语句执行成功!")
二、代码如下:
#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "gloryroad" ,
db = "grdb",
charset = "utf8"
)
使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
插入一条数据
#insert = cursor.execute("insert into user values(1,'Tom','123','1990-01-01')")
#print(u"添加语句受影响的行数:", insert)
另一种插入数据方法,通过格式字符串传入值,此方式可以防止sql注入
sql = "insert into user values(%s, %s, %s, %s)"
j=0
for i in range(100,201):
insert = cursor.execute(sql, (i,'lucy'+str(i),'efg'+str(i),str(1900+j)+'-02-01'))
j+=1
关闭游标
cursor.close()
提交事务
conn.commit()
关闭数据库连接
conn.close()
print(u"sql语句执行成功!")