Use MySQL from Anaconda (python)

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

environment

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

Driver installation

The driver is

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()

Recommended Posts

Use MySQL from Anaconda (python)
Use MySQL from Python
Use MySQL from Python
Use thingsspeak from python
Touch MySQL from Python 3
Use fluentd from python
Use Python 3.8 with Anaconda
Use BigQuery from python.
Use mecab-ipadic-neologd from python
Anaconda updated from 4.2.0 to 4.3.0 (python3.5 updated to python3.6)
Use e-Stat API from Python
Use Stanford Core NLP from Python
Read and use Python files from Python
Forcibly use Google Translate from python
Use kabu Station® API from Python
How to use Mysql in python
Use Azure Blob Storage from Python
Use the Flickr API from Python
Use fastText trained model from Python
Use Google Analytics API from Python
Receive textual data from mysql with python
Python 3.3 in Anaconda
Use PostgreSQL data type (jsonb) from Python
Use machine learning APIs A3RT from Python
I want to use jar from python
sql from python
Connecting from python to MySQL on CentOS 6.4
Use Google Cloud Vision API from Python
MeCab from Python
pyenv + anaconda + python3
Use Django from a local Python script
Use C ++ functions from python with pybind11
Firebase: Use Cloud Firestore and Cloud Storage from Python
Study from Python Hour7: How to use classes
[Bash] Use here-documents to get python power from bash
Wrap C with Cython for use from Python
Use Python in your environment from Win Automation
I want to use ceres solver from python
[Node-RED] Execute Python on Anaconda virtual environment from Node-RED [Anaconda] [Python]
Use Python in Anaconda environment with VS Code
Let's use different versions of SQLite3 from Python3!
Wrap C ++ with Cython for use from Python
From Python environment construction to virtual environment construction with anaconda
Use the nghttp2 Python module from Homebrew from pyenv's Python
Use Tor to connect from urllib2 [Python] [Mac]
Python: Use zipfile to unzip from standard input
Use config.ini in Python
Operate Filemaker from Python
[Python] Use JSON with Python
Use dates in Python
Access bitcoind from python
Changes from Python 3.0 to Python 3.5
Changes from Python 2 to Python 3.0
Python from or import
Use Valgrind in Python
Use mecab with Python3
Use LiquidTap Python Client ③
Run python from excel
Install python from source
Use DynamoDB with Python
Execute command from Python