欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Python中LOADDATAINFILE语句导入数据到MySQL遇到问题的解决方案分享

程序员文章站 2022-05-20 18:49:33
问题:programmingerror: (1064, "you have an error in your sql syntax; check the manual that corre...

问题:programmingerror: (1064, "you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'row 1' at line 1")

代码如下:

# -*- coding: utf-8 -*-

"""

created on fri apr 20 10:22:25 2018

@author: lingtingwu

"""

import os;

import pandas;

import mysqldb;

import matplotlib;

import matplotlib.pyplot as plt;

connection = mysqldb.connect(

host='localhost',

user='root',

passwd='123456',

db='7.1',

charset='utf8'

);

#导入数据到mysql

#遍历目录,把里面的所有数据入库

rootdir="d:\\python\\7.1\\";

def exesql(sql):

print("exesql: " + sql)

connection.query(sql);

for filename in os.listdir(rootdir):

path = os.path.join(rootdir, filename)

if ".txt" in filename:

print(path)

path = path.replace("\\", "\\\\");

#拿到文件名中的时间字段

datetime = filename[0:8];

tablename = "订购明细" + datetime;

#建表、导入数据到表中

exesql("drop table if exists " + tablename)

exesql("create table " + tablename + "(订单编号 int, 订购日期 datetime, 用户id int, 产品 char(8), `单价(元)` int, 数量 int, 订购金额 int);");

exesql("load data local infile '" + path + "' into table `" + tablename + "` character set 'utf8' columns terminated by ',' lines terminated by '\\n' ignore 1lines;");#把row改成lines

#建立日订购统计表

exesql("create table if not exists 日订购统计表(统计日期 date, 订购用户数 int, 订购次数 int, 人均订购金额 double, 订购总额 double);");

exesql("delete from 日订购统计表 where 统计日期='" + datetime + "';");

exesql("insert into 日订购统计表 select '" + datetime + "', count(distinct 用户id), count(用户id), sum(订购金额)/count(distinct 用户id), sum(订购金额) from `" + tablename + "`;");

statday = pandas.read_sql('select * from 日订购统计表;', con=connection);

#记得用完要关闭连接

connection.close();

print('\n日订购统计表数据如下:');

print(statday);

#绘图

font = {

'family' : 'simhei'

}

matplotlib.rc('font', **font);

plot1 = plt.plot(statday['统计日期'], statday['订购用户数']);

plot2 = plt.plot(statday['统计日期'], statday['订购次数']);

plot3 = plt.plot(statday['统计日期'], statday['人均订购金额']);

plt.show();

plt.legend(('订购用户数', '订购次数', '人均订购金额'))

解决方案:

把row改成line(python 3.6版本)。