Python mysql create database

2. MySQL with Python¶

The ‘mysql-connector’ is not supported by Django-framework. The good option is ‘mysqlclient’ which is supported by Django as well.

$ mysql -u root -p Enter password: mysql> CREATE DATABASE pythonSQL; 

2.2. Connect and load data¶

Following code can be used to connect and load the data to database. Note that, the commands in the c.execute(…) statements are exactly same as the commands in the previous chapters.

# create_fill_database.py import mysql.connector as mc # connect to database conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL') c = conn.cursor() # cursor to perform operations def create_table(): """ Create table in the database """ # optional: drop table if exists c.execute('DROP TABLE IF EXISTS writer') c.execute('CREATE TABLE writer \ ( \ id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, \ name VARCHAR(30) NOT NULL UNIQUE, \ age int \ )' ) def insert_data(): """ Insert data to the table """ c.execute("INSERT INTO writer (name) VALUES ('Pearl Buck')") c.execute(" INSERT INTO writer VALUES \ (NULL, 'Rabindranath Tagore', 80), \ (NULL, 'Leo Tolstoy', 82)" \ ) c.execute(" INSERT INTO writer (age, name) VALUES \ (30, 'Meher Krishna Patel')" \ ) def commit_close(): """ commit changes to database and close connection """ conn.commit() c.close() conn.close() def main(): """ execute create and insert commands """ create_table() insert_data() commit_close() # required for save the changes # standard boilerplate to call main function if __name__ == '__main__': main() 

$ python create_fill_database.py

2.3. Read data from table¶

Following code can be used to read data from the table,

# read_database.py import mysql.connector as mc conn= mc.connect(host='localhost',user='root',password='d',db='pythonSQL') c = conn.cursor() def read_data(): c.execute('SELECT * FROM writer') writers = c.fetchall() # data is read in the form of list for writer in writers: # print individual item in the list print(writer) # data at each row is saved as tuple def main(): read_data() if __name__ == '__main__': main() 

$ python read_database.py (1, 'Pearl Buck', None) (2, 'Rabindranath Tagore', 80) (3, 'Leo Tolstoy', 82) (4, 'Meher Krishna Patel', 30) 

• In this way, we can get the data from the table and perform various operations on the data.
• Also, we can use all those queries with python, as queries in the execute statements are same as queries in previous chapter.

2.4. Connection in try-except block¶

We can use following code to put the connection string in the try except block, so that we can get proper message for not connecting with the database,

# connect_try.py import mysql.connector as mq from mysql.connector import errorcode try: conn = mq.connect(host='localhost', user='root', password='d', db='pythonSQL') print("Connected") except mq.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(err) else: print("Connection closed") conn.close() 

$ python connect_try.py Connected Connection closed

$ python connect_try.py Something is wrong with your user name or password

$ python connect_try.py Database does not exist

© Copyright 2017, Meher Krishna Patel. Revision 31d452b4 .

Versions latest Downloads pdf html epub On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.

Источник

Python MySQL Create Database

To create a database in MySQL, use the «CREATE DATABASE» statement:

Example

create a database named «mydatabase»:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«
)

mycursor.execute(«CREATE DATABASE mydatabase»)

If the above code was executed with no errors, you have successfully created a database.

Check if Database Exists

You can check if a database exist by listing all databases in your system by using the «SHOW DATABASES» statement:

Example

Return a list of your system’s databases:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«
)

Or you can try to access the database when making the connection:

Example

Try connecting to the database «mydatabase»:

mydb = mysql.connector.connect(
host=»localhost»,
user=»yourusername«,
password=»yourpassword«,
database=»mydatabase»
)

If the database does not exist, you will get an error.

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

Thank You For Helping Us!

Your message has been sent to W3Schools.

Top Tutorials

Top References

Top Examples

Get Certified

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.

Источник

Create a MySQL Database using Python (pymySql)

In this tutorial, we will learn how to create a SQL database using Python and the MySQL relational database management system.

You can save data to a MySQL database easily using the pyMySQL package in Python.

Getting Started

Before you can get started, you should have two things:

Make sure that you have Apache and MySQL activated.

You can access your database using PHPMyAdmin.

To do so, open a browser and type localhost/phpmyadmin .

Create Your First MySQL Database Using Python

Now that MySQL and PHPMyAdmin are installed on your computer, you can create your first MySQL Database with Python using the pymysql package.

Install and Load Libraries

The first step is to install and load the pymysql library.

To install the library in Anaconda, open the Anaconda Prompt.

Add this command to the prompt:

conda install -c anaconda pymysql

Press “Y” when you are asked to do so.

The package should be downloaded successfully.

Then, in your Python Script, import the package.

Create the MySQL Database

Now, we are going to establish a connection with the database.

connection = pymysql.connect(host='localhost', user='root', port='', password='')

Then, we need to simulate the “ CREATE DATABASE ” MySQL function using cursor() .

Here, I create a “ gsc_db ” database because I want to backup Google Search Console data.

try: with connection.cursor() as cursor: cursor.execute('CREATE DATABASE gsc_db') finally: connection.close()

Remember that it is always good to close the connection after the operation using the close() command.

Let’s see if the database was successfully created.

Go back to PHPMyAdmin.

Allright, it is there. We can now create a table in the database.

Create the Table in MySQL

To create a new table in my gsc_db database, I will need to re-establish the connection that I closed earlier.

This time, I will establish the connection to the gsc_db database.

connection = pymysql.connect(host='localhost', user='root', port='', password='', db='gsc_db', cursorclass=pymysql.cursors.DictCursor)

The pymysql.cursors.DictCursor is the object you use to interact with the database and return the result as a dictionary.

Then, I will create the backup_gsc table. I will create it only if it does not exist using the CREATE TABLE IF NOT EXISTS function.

try: with connection.cursor() as cursor: sqlQuery = '''CREATE TABLE IF NOT EXISTS backup_gsc(Date DATE, Page TEXT, Query TEXT, Clicks INT, Impressions INT, Ctr DECIMAL(10,2), Position DECIMAL(3,2))''' cursor.execute(sqlQuery) finally: connection.close()

Here, I setted-up columns to the table and gave them datatypes. This set-up is based on the data that I want to extract from Google Search Console.

If you don’t know which data type you should give to your columns, look at the cheat sheet.

The DECIMAL(3,2) datatype means that I want to log 3 digits before the decimal, 2 after the decimal.

Congratulations, your database should be created.

To see if it worked, go to PHPMyAdmin and click on Structure.

You should have your empty table created using the columns you specified.

Full Python Code

import pymysql.cursors # Create a mySQL Database # Establish connection connection = pymysql.connect(host='localhost', user='root', port='', password='') # Simulate the CREATE DATABASE function of mySQL try: with connection.cursor() as cursor: cursor.execute('CREATE DATABASE gsc_db') finally: connection.close() # Create a table connection = pymysql.connect(host='localhost', user='root', port='', password='', db='gsc_db', cursorclass=pymysql.cursors.DictCursor) try: with connection.cursor() as cursor: sqlQuery = '''CREATE TABLE IF NOT EXISTS backup_gsc(Date DATE, Page TEXT, Query TEXT, Clicks INT, Impressions INT, Ctr DECIMAL(10,2), Position DECIMAL(3,2))''' cursor.execute(sqlQuery) finally: connection.close()

Load Data To Your Table

Next, we are going to see how you can save your Google Search Console data in the MySQL table that you just created.

Источник