Use MySQL from Anaconda (python)

How to access MySQL from plain Anaconda. Python takes a lot of time to access MySQL.

environment

• anaconda3-2.4.0(Python3.5.1)

Even with> 2.7.x, it works if you take () of print (). That's the difference.

Driver installation

The driver is

• mysql-connector-python
• PyMySQL

I will try two of them.

mysql-connector-python edition

For the time being, use this.

conda install -c https://conda.anaconda.org/anaconda mysql-connector-python

Use (read)

For some reason, it's written a bit like PHP. DB schema etc. are omitted.

#coding:utf-8
import mysql.connector

#Connection information
dbh = mysql.connector.connect(
        host='localhost',
        port='3306',
        db='testdb',
        user='dbuser',
        password='password',
        charset='utf8'
    )

#Get cursor
stmt = dbh.cursor(buffered=True)

#SQL
sql = "select * from members"

#Run
stmt.execute(sql)

#Get
rows = stmt.fetchall()

#loop
for row in rows:
    print(row[1])

#clean up
stmt.close()
dbh.close()

Use (write)

Postscript: I thought I would just change the SQL for reading, but it's a little different. It looks like you need to commit ().

#coding:utf-8
import mysql.connector

#Connection information
dbh = mysql.connector.connect(
        host='localhost',
        db='testdb',
        user='dbuser',
        password='password'
    )

#Get cursor
stmt = dbh.cursor(buffered=True)

#SQL
sql = "insert into members(name) values('foo');"

#Run
stmt.execute(sql)

#commit
dbh.commit()

#clean up
stmt.close()
dbh.close()

reference

There was a good article in here.

Py MySQL edition

Here was helpful. Also, Honke? Site.

In my environment, only PyMySQL worked with Django. ..

Installation

pip install PyMySQL

Use (read)

The code looks like this. It's slightly different.

#coding:utf-8
import pymysql

#Connection information
dbh = pymysql.connect(
		 host='localhost',
	     user='dbuser',
	     password='password',
	     db='testdb',
	     charset='utf8',
	     cursorclass=pymysql.cursors.DictCursor
    )

#cursor
stmt = dbh.cursor()

#SQL
sql = "select * from auth_user"

#Run
stmt.execute(sql)

#Get
rows = stmt.fetchall()

#loop
for row in rows:
	print(row)

#clean up
stmt.close();
dbh.close();

Use (write)

You still need commit ().

#coding:utf-8
import pymysql

#Connection information
dbh = pymysql.connect(
         host='localhost',
         user='dbuser',
         password='password',
         db='testdb',
         charset='utf8',
         cursorclass=pymysql.cursors.DictCursor
    )

#cursor
stmt = dbh.cursor()

#SQL
sql = "insert into members(name) value('pymysql1')"

#Run
stmt.execute(sql)

#commit
dbh.commit()

#clean up
stmt.close()
dbh.close()