mysql基本用法(二)安装pymysql及游标使用(1)

首先安装一下 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语句执行成功！")