如何在 Python3 中操作数据

安装

conda install pymysql

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', passwd='123456', db='db_jpa')

# drop DATABASE IF  EXISTS pymysql
CREATE_DATABASE_SQL = """
CREATE DATABASE IF NOT EXISTS pymysql DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
"""
conn.query(CREATE_DATABASE_SQL)

CREATE_TABLE_SQL = """
CREATE TABLE mytb(
  title CHAR(20) NOT NULL ,
  keywd CHAR(30)
)
"""

conn.query(CREATE_TABLE_SQL)

INSERT_SQL = """
INSERT INTO mytb(title,keywd) VALUES ('first title','firstkeywd')
"""

conn.query(INSERT_SQL)

注意：要使用

conn.commit()

提交事务。

插入一些数据，下面我们做查询：

INSERT INTO mytb(title,keywd) VALUES ('Java 程序员','Spring MVC、Spring Boot'),
('Python 程序员',' Web、爬虫、机器学习、数据科学'),
('PHP 程序员','网页制作、后台开发'),
('C++ 程序员','嵌入式、游戏'),
('产品经理','吹牛');

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', passwd='123456', db='pymysql')
cs = conn.cursor()

cs.execute("select * from mytb")

for i in cs:
    print("当前是第 %s 行" % str(cs.rownumber), end=" ")
    print("标题是 %s" % i[0], end=" ")
    print("关键字是 %s" % i[1])

---

# -*- coding=utf-8 -*-

import pymysql.cursors

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='123456',
                             db='wikiurl',
                             charset='utf8mb4')

try:
    # 获取会话指针
    with connection.cursor() as cursor:
        sql = " select `urlname`,`urlhref` from  `urls` where `id` is not null"
        count = cursor.execute(sql)
        print(count)

        # 查询数据
        # result = cursor.fetchall()
        # print result

        # 查询数据
        result = cursor.fetchmany(size=3)
        print(result)
finally:
    connection.close()