Python连接数据库(查询、插值)获取查询结果某一列或某几列存为list类型数据,循环对时间类型数据运算

1.安装pymysql

 pip install pymysql

2.创建数据库连接池

# 这里都是本地mysql的一些信息 db_name即database选择要查询的库
db_name = 'mybatis'
db_username = 'root'
db_password = '123456'
db_host = 'localhost'    
db_port = 3306

3.连接数据库获取列表 方法众多 我采用常用的直接在函数中执行语句

def getList():
    '''
    查询数据库某一列保存为list
    :return:None
    '''
   
    conn = connect(host=db_host, port=db_port, database=db_name,
                   user=db_username,
                   password=db_password, charset='utf8',
                   )
    cs1 = conn.cursor()
    sql1 = "select * from testStr"
    cs1.execute(sql1)



    datalist = []
    alldata = cs1.fetchall()
    for s in alldata:
        datalist.append([s[1],s[2]])
        # datalist.append(s[2])
    print(datalist)
    return datalist

参考1:简单易懂的连接操作(3条消息) python | 连接MySQL数据库的简单方法_黄佳俊、的博客-CSDN博客_python连接mysqlhttps://blog.csdn.net/weixin_48419914/article/details/122062831?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165269327216781667889300%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=165269327216781667889300&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-2-122062831-null-null.142^v9^pc_search_result_control_group,157^v4^control&utm_term=python%E8%BF%9E%E6%8E%A5mysql%E6%95%B0%E6%8D%AE%E5%BA%93&spm=1018.2226.3001.4187 参考2: 带异常处理的连接操作(3条消息) Python中连接mysql数据库，并操作数据库_公子清羽的博客-CSDN博客https://blog.csdn.net/weixin_42550871/article/details/108912098?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165269327216781667889300%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=165269327216781667889300&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-1-108912098-null-null.142^v9^pc_search_result_control_group,157^v4^control&utm_term=python%E8%BF%9E%E6%8E%A5mysql%E6%95%B0%E6%8D%AE%E5%BA%93&spm=1018.2226.3001.4187

3.获取查询结果

这个大概是testStr表的结构,我想获取列表的经纬度 修改2.代码中的索引即可,原理就是读取数据库之后每一行作为一个元素,取元素的第几个索引append到列表中

 4.获取到list集合之后 向其中s_time列插值

例子:将list b按照5s为间隔插入到第十条记录之后

b = [[12.454553604125977, 54.55555], [14.546456336975098, 55.55555], [15.543455123901367, 45.6556656],[1,2],[3,4]]

5.时间类型数据计算

引入datetime库

import datetime #获取时间的库
from datetime import datetime
from datetime import timedelta

sql插值函数

def insert(sql,db_data=()):
    '''
    循环向数据库中插值
    :return:
    '''
    try:
        conn = connect(host=db_host, port=db_port, database=db_name,
                       user=db_username,
                       password=db_password, charset='utf8',
                       )
        cursor = conn.cursor()
    except Exception as e:
        print(e)
        logging.error('数据库连接失败:%s' % e)
        return False

    try:
        cursor.execute(sql, db_data)
        conn.commit()
    except Exception as e:
        conn.rollback()
        logging.error('数据写入失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    return True
# 这是第二种连接数据库的写法调用的话就在下面写
sql = " insert balabala values(%s %s %...)"
data = (要插回去的的数据 这里是->(b[i][0],b[i][1],time) ) 

'''获取当前时间'''
nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
testTime = '2022-05-16 10:28:56'
'''测试时间加减法'''
print(time1)
plustime1 = time1 + timedelta(seconds=5)
print("+5s:",plustime1)

'''测试循环添加时间类型数据'''
timeList = []
for i in range(5):
    time1 = time1 + timedelta(seconds=5)
    timeList.append(time1)
for i in timeList:
    print(i)

testTime = '2022-05-16 10:28:56'
# 循环向数据库中插值
time1 = datetime.strptime(str(testTime),'%Y-%m-%d %H:%M:%S')

for i in range(len(b)):
    # timeList.append(time1)
    time1 = time1 + timedelta(seconds=20)
    sql = "insert into testStr(longitude,latitude,s_time) values (%s,%s,%s)"
    data = (b[i][0],b[i][1],time1)
    result = insert(sql, data)

其中时间运算主要是用到了datetime里的timedelta函数(hour minute second) 参考如下:

(2条消息) Python对时间数据进行运算_佐佑思维的博客-CSDN博客_python 时间运算https://blog.csdn.net/weixin_45288557/article/details/112102993?ops_request_misc=&request_id=&biz_id=102&utm_term=python%E6%97%B6%E9%97%B4%E6%95%B0%E6%8D%AE%E5%8A%A05%E7%A7%92&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-4-112102993.142^v9^pc_search_result_control_group,157^v4^control&spm=1018.2226.3001.41876.最终结果 

可能有点乱了 但是一个函数一个函数的写下来最终是能实现效果的

即按照5s循环向数据库中插入我们想要的数据