Shulou(Shulou.com)06/02 Report--

This article shows you how to import the CSV file into the MySQL database in Python, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

There are generally two ways to import CSV files into a database:

1. Import one by one through SQL's insert method, which is suitable for CSV files with small amount of data. I won't repeat it here.

2. Import through load data method, which is fast and suitable for big data files, is also the focus of this article.

The sample CSV file is as follows:

The overall work is divided into three steps:

1. Use python to connect to mysql database. Please refer to how to use python to connect to database.

2. Create a table based on the table field of CSV file

3. Use the load data method to import the contents of the CSV file

Introduction to load data syntax:

LOAD DATA LOCAL INFILE 'csv_file_path' INTO TABLE table_name FIELDS TERMINATED BY', 'LINES TERMINATED BY'\\ r\\ n' IGNORE 1 LINES

Csv_file_path refers to the absolute path of the file

Table_name refers to the table name

FIELDS TERMINATED BY', 'means separated by commas

LINES TERMINATED BY'\ r\\ n' refers to line feeds

IGNORE 1 LINES means to skip the first row because the first row is the field name of the table

The full code is given below:

The full code is given below:

# Import pymysql method

Import pymysql

# connecting to the database

Config = {'host':''

'port':3306

'user':'evdata'

'passwd':''

'charset':'utf8mb4'

'local_infile':1

}

Conn = pymysql.connect (* * config)

Cur = conn.cursor ()

# load_csv function. The parameters are csv file path, table name, and database name.

Def load_csv (csv_file_path,table_name,database='evdata'):

# Open csv file

File = open (csv_file_path, 'ringing dagger encodingling colors utfmur8')

# read the first line field name of the csv file and create a table

Reader = file.readline ()

B = reader.split (',')

Colum =''

For an in b:

Colum = colum + a + 'varchar (255),'

Colum = colum [:-1]

# Writing sql,create_sql is responsible for creating tables, data_sql is responsible for importing data

Create_sql = 'create table if not exists' + table_name +'+'('+ colum +')'+ 'DEFAULT CHARSET=utf8'

Data_sql = "LOAD DATA LOCAL INFILE'% s' INTO TABLE% s FIELDS TERMINATED BY', 'LINES TERMINATED BY'\\ r\ n' IGNORE 1 LINES"% (csv_filename,table_name)

# using the database

Cur.execute ('use% s'% database)

# set the encoding format

Cur.execute ('SET NAMES utf8;')

Cur.execute ('SET character_set_connection=utf8;')

# execute create_sql to create a table

Cur.execute (create_sql)

# execute data_sql and import data

Cur.execute (data_sql)

Conn.commit ()

# close the connection

Conn.close ()

Cur.close ()

Problems that have occurred:

I am using the win10 system, and the following error occurs when the code is executed

ERROR 1148 (42000): The used command is not allowed with this MySQL version.

The reason is that the command load data is not supported

Solution:

The configuration file needs to be changed

Locate the my.ini configuration file in the mysql installation directory and copy the following to the file

[mysqld] # server configuration

Local-infle = 1

[mysql] # client configuration

Local-infile = 1 the above is how to import CSV files into MySQL database in Python. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.