First, introduce some basic operations of SQL database:

1 create 2 delete 3 write 4 update (modify) 5 condition selection

With the above basic operations, you can establish and store a simple database.

Release the code called by python: here is calling dos to operate the database, which is not as simple as the following

# -*- coding: utf-8 -*-
"""
Created on Mon May  6 09:59:32 2019

@author: wenzhe.tian
"""


import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "root", "twz1478963", "TESTDB", charset='utf8' )  
# Use cursor()Method get operation cursor 
cursor = db.cursor()

# If the data table already exists using execute() Method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
print(cursor.fetchone())
cursor.execute("SELECT VERSION()")


# Create data table SQL Sentence
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT );"""

cursor.execute(sql)

### % replace
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s );" % \
       ("'Mac'", "'Mohan'", 20, "'M'", 9000)
         
         
         
try:
   # implement sql Sentence
   cursor.execute(sql)
   print(cursor.fetchone())
   # Commit to database execution
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
   
### % replace
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s );" % \
       ("'John'", "'Will'", 24, "'M'", 12000)
         
         
         
try:
   # implement sql Sentence
   cursor.execute(sql)
   print(cursor.fetchone())
   # Commit to database execution
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
   
   
sql = "SELECT * FROM EMPLOYEE \
       WHERE first_name like %s" % ("'%h_'");
       
'''
WHERE A and/or B between in(A,B)
% Represents multiple word values,_ Underline indicates a character;
M% : It means that the fuzzy query information is M The first.
%M% : Indicates that the query contains M All of.
%M_ : Represents a query to M Everything in the penultimate
'''
       
# DELETE FROM EMPLOYEE WHERE AGE <20       
       
try:
   # implement SQL Sentence
   cursor.execute(sql)
   # Get all records list
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Print results
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fecth data")
   
# Close database connection
db.close()

The above code will write the form of characters in SQL language and call the interface to perform the operation.

Here are some examples of storing excel to the new database as a reference: here is to call the SQL of dataframe to perform the operation. Note the Chinese conversion of encoding='gbk '

The form name does not need to be created in advance when writing to the database.

# -*- coding: utf-8 -*-
"""
Created on Tue May  7 15:40:23 2019

@author: ext.wenzhe.tian
"""


from sqlalchemy import create_engine
import pandas as pd

host = '127.0.0.1'
port= 3306
db = 'beilixinyuan'
user = 'root'
password = 'twz1478963'

engine = create_engine(str(r"mysql+mysqldb://%s:" + '%s' + "@%s/%s?charset=utf8") % (user, password, host, db))



try:
#   df = pd.read_csv(r'D:\2PHEV_v3.csv',encoding='gbk')
#   read  
    table='sale_phev'
    sql = "SELECT * FROM "+'%s' %(table)
    df=pd.read_sql(sql,con=engine)
#    Write in
#    df.to_sql('sale_ev', con=engine, if_exists='append', index=False)
except Exception as e:

    print(e.message)
    


# Export method 2
#'''
#WHERE A and/or B between in(A,B)
#% Represents multiple word values,_ Underline indicates a character;
#M% : It means that the fuzzy query information is M The first.
#%M% : Indicates that the query contains M All of.
#%M_ : Represents a query to M Everything in the penultimate
#'''
#
#
#
import MySQLdb
import pandas as pd
# Open database connection
db = MySQLdb.connect("localhost", "root", "twz1478963", "beilixinyuan", charset='utf8' )  
# Use cursor()Method get operation cursor 
cursor = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)


sql = "SELECT * FROM sale_ev"
       

# DELETE FROM EMPLOYEE WHERE AGE <20       
       
try:
   # implement SQL Sentence
   cursor.execute(sql)
   # Get all records list
   results = cursor.fetchall()
   pd.read_sql(sql,con=engine)
 
except:
   print ("Error: unable to fecth data")
   
h=list(results)
df=pd.DataFrame(h)
del results
del h