PyMySQL in Pycharm

Hey guys! We haven’t met in my Python channel for a while. And recently, I received plenty of puzzles about using pymysql in python from your guys. To be efficient, I choose a less time-consuming way to clear every barrier for you. Following the steps below, you can understand throughoutly and run programme correctly. Here we go!

PyMySQL in Pycharm

1. First, according to the slides, we are just told to import pymysql at the beginning. But easily, you will get an error report. It is because pymysql is from external liberaries. You should open your ternimal and install this package using pip install PyMySQL. Wait for a second, it will be installed successfully.
2. And then, if you are going to try the code provided from slides, you will get an error unexpectedly.

import pymysql

db = pymysql.connect('localhost', 'root', '', 'employee')

cursor = db.cursor()

cursor.execute('SELECT VERSION()')

data = cursor.fetchone()
print("Database version: %s" % data)

db.close()

3. Don’t worry! It is just because that you have one more external library which should be installed. Similar as before, you can input pip install cryptography in your terminal. If you get successful, you will see this below. (I am using my cache to install it, but you should see three progressing bars.)

4. Now if you want to try to run the code, it might be some problems. Because most of you connect your mysql service by XAMPP, it might run successfully. However, do you really understand the code this line?

db = pymysql.connect('localhost', 'root', '', 'test')

5. Besides, if you connect your mysql service by MySQL, I believe you will get an error like this.

6. The reason why you get such an error is that you do not have a database called “test”, and you might skip your password to login your mysql service. So we should specify and assign the parameter in connect() function.

7. Here is a copy from official documentary of Connection() class. You can get this description by pymysql.conncections.Conncections(). The parameters of pymysql.connect() are the parameters of the Connection() constructor.

# This is class description of Connection() constructor from connections.py
class Connection(object):
    """
    Representation of a socket with a mysql server.

    The proper way to get an instance of this class is to call
    connect().

    Establish a connection to the MySQL database. Accepts several
    arguments:

    :param host: Host where the database server is located
    :param user: Username to log in as
    :param password: Password to use.
    :param database: Database to use, None to not use a particular one.
    :param port: MySQL port to use, default is usually OK. (default: 3306)
    :param bind_address: When the client has multiple network interfaces, specify
        the interface from which to connect to the host. Argument can be
        a hostname or an IP address.
    :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
    :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)
    :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)
    :param charset: Charset you want to use.
    :param sql_mode: Default SQL_MODE to use.
    :param read_default_file:
        Specifies  my.cnf file to read these parameters from under the [client] section.
    :param conv:
        Conversion dictionary to use instead of the default one.
        This is used to provide custom marshalling and unmarshaling of types.
        See converters.
    :param use_unicode:
        Whether or not to default to unicode strings.
        This option defaults to true for Py3k.
    :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
    :param cursorclass: Custom cursor class to use.
    :param init_command: Initial SQL statement to run when connection is established.
    :param connect_timeout: Timeout before throwing an exception when connecting.
        (default: 10, min: 1, max: 31536000)
    :param ssl:
        A dict of arguments similar to mysql_ssl_set()'s parameters.
    :param read_default_group: Group to read from in the configuration file.
    :param compress: Not supported
    :param named_pipe: Not supported
    :param autocommit: Autocommit mode. None means use server default. (default: False)
    :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
    :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
        Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
    :param defer_connect: Don't explicitly connect on contruction - wait for connect call.
        (default: False)
    :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.
        The class will take the Connection object as the argument to the constructor.
        The class needs an authenticate method taking an authentication packet as
        an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used
        (if no authenticate method) for returning a string from the user. (experimental)
    :param server_public_key: SHA256 authenticaiton plugin public key value. (default: None)
    :param db: Alias for database. (for compatibility to MySQLdb)
    :param passwd: Alias for password. (for compatibility to MySQLdb)
    :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)

    See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the
    specification.
    """

8. You can see the parameters in the constructor. It has host, user, password, database, port and so on. Now you might know why you cannot run your code successfully. It is just because the value to the corresponding parameter is wrong.
9. Next, you should specify and assign the value to the parameter one by one. Remove the code provided in slides (that’s wrong) and use the following.

import pymysql

db = pymysql.connect(host='localhost', user='root', passwd='', db='employee') # new changed

cursor = db.cursor()

cursor.execute('SELECT VERSION()')

data = cursor.fetchone()
print("Database version: %s" % data)

db.close()

10. Because I set my password as null and I do not have a database called “test”, I just use “employee” database (you should check your own databases). If successful, you can get your version of mysql service.
    

11. If you still want to use “test” database, for most of you, you should change your connect port. In my machine, the default port 3306 cannot connect “test” database because it is an occupied port by MySQL. And my XAMPP is using 3316 port I set in config file my.ini. You can see my code and result to get understood.

import pymysql

# Here I can use "test" database 
# because this database is built-in database in MariaDB (XAMPP)
# And also I change my port to 3316
db = pymysql.connect(host='localhost', user='root', passwd='', db='test', port=3316)

cursor = db.cursor()

cursor.execute('SELECT VERSION()')

data = cursor.fetchone()
print("Database version: %s" % data)

db.close()

12. I hope you can understand this long article to explain a tiny part of pymysql usage. For a further knowledge, you can read this documentary.

So far, you have learnt and understood the basic of PyMySQL in python. Hopefully, it helps.