Shulou(Shulou.com)05/31 Report--

Most people do not understand the knowledge points of this article "how to use Python to play with MySQL database", so the editor summarizes the following contents, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to use Python to play with MySQL database" article.

Background I do the connection experiment in Anaconda notebook, the environment Python3.6, of course, it can also be operated in Python Shell.

The most common and stable python library for connecting to MySQL databases is PyMySQL.

2. Basic operation 1. The easiest way to install PyMySQL library:

Enter pip install pymysql on the command line

Or:

Download the whl file [1] to install, the installation process is Baidu.

2. There are two ways to install MySQL database and MySQL database:

MySQL and MariaDB

I use MariaDB, which is a branch of MySQL.

The two are compatible in most of the performance, and there is no difference in using them.

Give the download address: MySQL [2], MariaDB [3], the installation process is very simple, all the way Next Step, but to remember the password.

In a small episode, MySQL and MariaDB are equivalent to the relationship between sisters and sisters, both of which were created by the same person (Widenius).

After MySQL was acquired by Oracle, Mr. Widenius felt uncomfortable, so he set up a MariaDB, which could completely replace MySQL.

Daniel is wayward.

3, SQL basic syntax below to use SQL table creation, query, data insertion and other functions, here a brief introduction to the basic sentences of SQL language.

View the database: SHOW DATABASES

Create database: CREATE DATEBASE database name

Use database: USE database name

View the data sheet: SHOW TABLES

Create data table: CREATE TABLE table name (column name 1 (data type 1), column name 2 (data type 2))

Insert data: INSERT INTO table name (column name 1, column 2) VALUES (data 1, data 2)

View data: SELECT * FROM table name

Update data: UPDATE table name SET column name 1 = new data 1, column name 2 = new data 2 WHERE a column = some data

4, connect to the database after installing the necessary files and libraries, then officially begin to connect to the database, although mysterious but not difficult!

# first import PyMySQL library import pymysql# connection database, create connection object connection# connection object: connect to database, send database information, handle rollback operation (when query is interrupted The database returns to its original state), Create a new cursor object connection = pymysql.connect (host = 'localhost' # host attribute user =' root' # username password ='*'# here fill in the login database password db = 'mysql' # database name ) execute this code and connect!

5. Add, delete, modify and search operations. First, check which databases are available:

# create a cursor object, a connection can have many cursors, and a cursor tracks a data state. # the cursor object functions as:, create, delete, write, query, etc. Cur = connection.cursor () # check which databases are available, and use cur.fetchall () to obtain all the results of query print (cur.fetchall ()) to print out all databases:

(('information_schema',), (' law',), ('mysql',), (' performance_schema',), ('test',)) create tables in the test database:

# use the database testcur.execute ('USE test') # to create table student in the test database with name column and age column cur.execute (' CREATE TABLE student (name VARCHAR (20), age TINYINT (3)') to insert a piece of data into the data table student:

Sql = 'INSERT INTO student (name,age) VALUES (% s)' cur.execute (sql, ('XiaoMing',23)) to view the contents of the data table student:

Cur.execute ('SELECT * FROM student') print (cur.fetchone ()) printout is: (' XiaoMing', 23)

Bingo! It's a piece of data that we just inserted.

Finally, remember to close the cursor and connect:

# close the connection object, otherwise it will lead to connection leakage and consume database resources connection.close () # close the cursor cur.close () OK, the whole process is roughly like this.

Of course, these are all very basic operations, and more usage needs to be found in the PyMySQL official documentation [4].

3. Import big data file. Take csv file as an example. There are generally two ways to import csv file into 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

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.

An introduction to sql's load data syntax:

LOAD DATA LOCAL INFILE 'csv_file_path' INTO TABLE table_name FIELDS TERMINATED BY', 'LINES TERMINATED BY'\ r\\ n' IGNORE 1 LINEScsv_file_path means file absolute path table_name refers to table name FIELDS TERMINATED BY', 'comma separated LINES TERMINATED BY'\\ r\\ n 'means newline IGNORE 1 LINES refers to skipping the first line, because the first row is the field name of the table

The full code is given below:

# Import pymysql method import pymysql# connection database config = {:'',: 3306,: 'username',:' password',: 'utf8mb4',: 1} conn = pymysql.connect (* * config) cur = conn.cursor () # load_csv function. The parameter is the csv file path, and the table name is Database name def load_csv (csv_file_path,table_name,database='evdata'): # Open the csv file file = open (csv_file_path, 'ringing theory encodingconversation conversation utfMur8') # read the first line field name of the csv file Create the table reader = file.readline () b = reader.split (',') colum =''for an in b: colum = colum + a +' varchar, 'colum = colum [:-1] # write sql,create_sql to create the table 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) # use the database cur.execute ('use% s'% database) # to set the encoding format cur.execute ('SET NAMES utf8 ') cur.execute (' SET character_set_connection=utf8 ') # execute create_sql, create table cur.execute (create_sql) # execute data_sql, import data cur.execute (data_sql) conn.commit () # close connection conn.close () cur.close () the above is about "how to use Python to play with MySQL database", I believe you all have some understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about it, please 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.