1

I'm a new Python developer but have successfully worked with python and mysql on a Windows machine. I am struggling to figure it out on my new Ubuntu platform (v 16.04). Here's what I've done .

My machine has both python 2.7 and 3.5 installed in usr/bin

I installed this mysql library

sudo apt-get install python-mysqldb

I got no errors.

I get into python3 and do

import MySQLdb

It gives an error no module named MySQLdb

I get into python (2.7)

The import works and I can do some queries.

So I don't really get what's going on with these multiple versions of python. I don't want to work in python 2.7. Did I install a mysqldb that only works in python 2.7?

Assuming the library isn't the correct one, I went to the Oracle Mysql site and downloaded the correct package for my Ubuntu distro and then tried to install it following their instructions:

sudo dpkg -i mysql-connector-python_2.1.6-1ubuntu16.10_all.deb

This won't install because :

dpkg: regarding mysql-connector-python_2.1.6-1ubuntu16.10_all.deb containing mysql-connector-python: mysql-connector-python conflicts with python-mysql.connector python-mysql.connector (version 2.0.4-1) is present and installed.

So now I'm in trouble the first package only works in python 2.7 but it's presence seems to now interfere with installing the mysql-connector-python.

I tried running 

sudo apt-get remove python-mysqldb

which completes without errors. But my next attempt at sudo dpkg -i mysql-connector-python_2.1.6-1ubuntu16.10_all.deb

still fails with the same message.

So I'm confused about a number of things:

Installing things when both pythons are on my machine and knowing which one (or both) they will affect.

Is the mysql-connector from Oracle the preferred one to use for Python 3.5 development? I saw some mention that it has bugs and that people use this python-mysqldb one instead but then I've seen some mention that it isn't supported for python 3.

So at this point I don't know which to use, how to get rid of what I've installed that is wrong, how to install the correct one. And then ultimately I'd like to see a couple lines of python that show me how to import the right package and test a connection.

dam
  • 353
  • 1
  • 7
  • 19
  • This question seems to be addressed here http://stackoverflow.com/questions/4960048/python-3-and-mysql – Isaac Osiemo Apr 19 '17 at 16:21
  • Possible duplicate of [Python 3 and MySQL](http://stackoverflow.com/questions/4960048/python-3-and-mysql) – Isaac Osiemo Apr 19 '17 at 16:22
  • There is some useful help on this other thread. Suggestions are made about various mysql libraries that work with Python3. All are with reference to how compatible they are with MySQLdb. Why is this important? Is MySQLdb some kind of standard? Can I get some pointers on how to clear out the MySQLdb package I installed so that I will be able to try installing something like oracle's mysql connector? Finally, for a small web site with not many users and no big speed requirements, can someone recommend one of the libraries that is easiest to work with and decently documented? – dam Apr 19 '17 at 17:47

3 Answers3

4

I followed the pointer suggested and chose to use mysqlclient because I may want to use Django and because it seems that compatibility with MySQLdb is important- maybe because its some kind of reference implementation of python sql driver...

Anyway Here's what I did.

$sudo apt-get install libmysqlclient-dev
$cd to project dir (myproj)
$virtualenv -p /usr/bin/python3 env
$source env/bin/activate
(env) pip install mysqlclient
(env) deactivate

TO use this python environment:

$source env/bin/activate
(env)python3
>>> import MySQLdb
>>> db = MySQLdb.connect(host="localhost", user="u", passwd="secret", db="mydb")
>>> cur = db.cursor()
>>> cur.execute("select * from a-table")
>>> for row in cur.fetchall():
       print(row[0])
>>> db.close()
dam
  • 353
  • 1
  • 7
  • 19
4

Python3 only answer.

Django and MySQL can be connected using different packages. One being python3-pymysql

Step 1: Run the command:- sudo apt-get install python3-pymysql

Step 2: Create and activate a python 3 virtual enviornment

If you are using virtualenv
    $ `virtualenv -p python3 venv`
    $ `source venv/bin/activate`
    $ `pip install pymysql`

If you are using virtualenvwrapper
    $ `mkvirtualenv venv --python=/usr/bin/python3`
    $ `pip install pymysql`

Step 3: Import the package and test for connections

 $ python
 >>> import pymysql
 >>> conn = pymysql.connect("localhost", "root", "password", "test_db")
 >>> x = conn.cursor()
 >>> sql = """SELECT * FROM `table1`"""
 >>> x.execute(sql)
Alex
  • 5,759
  • 1
  • 32
  • 47
sudoabhinav
  • 41
  • 2
0

You can connect with mysql-connector-python library as below:

1: Install mysql connector

`pip install mysql-connector-python`

2: Create yaml or ini file for credentials (say credentials.yml)

 `mysql:
   username: "<username>"
   password: "<password>"
   hostname: "<host>"
   db: "<databaseName>"`

3: Create python class for connecting from mysql

`import mysql.connector
 import yaml

 class MySQLConnection:
     def __init__(self):
         credentials = yaml.load(open("credentials.yml"), 
         Loader=yaml.FullLoader)
         self.host = credentials['mysql']['hostname']
         self.user = credentials['mysql']['username']
         self.password = credentials['mysql']['password']
         self.db = credentials['mysql']['db']
         self.conn = mysql.connector.connect(host=self.host,              
         user=self.user, 
         password=self.password,
         database=self.db)
         print("INFO: Database connected!!!")`
Divya Nambiar
  • 69
  • 2