In this tutorial, we are going to learn how to import MySQL database using phpMyAdmin. There are two ways to do the import via this PHP application.

1. Go to the “Import” tab in the phpMyAdmin and upload a file(SQL, CSV …) source that contains the database dumb.
2. Choose a database and drag and drop the import file to the phpMyAdmin interface.

How to import?

Open the phpMyAdmin application and create a connection by logging in with the host, user and password. Then, follow the below steps to import a database.

1) Choose the “Database” link and create a new or select an existing database. In a previous tutorial, we have seen the possible options of creating a database using phpMyAdmin.

2) Choose the file in .sql (or other phpMyAdmin-supported) format.

3) [optional] Choose char-set, SQL compatibility modes and other options like,

• Foreign key checks.
• Partial import.

Click “Go” to complete the import.

Import CSV

If you have the SQL dumb in a form of a CSV file, the phpMyAdmin allows that format to import.

Change the format to the CSV from the default format. The SQL is the default format that the phpMyAdmin populates under the “Format” section.

It is suitable to import a database containing a single table. If the import file contains multiple tables then the import will merge all into one.

It creates auto-generated columns like COL1, COL2… and stores the other comma-separated values as data.

Note the following CSV format to import a database table.

"id","question","answer" "1"," What are the widely used array functions in PHP?","Answer1" "2","How to redirect using PHP?","Answer2" "3"," Differentiate PHP size() and count():","Answer3" "4","What is PHP?","Answer4" "5","What is php.ini?","Answer5"

Import large SQL file

Note the maximum file size allowed to upload via the phpMyAdmin application. It is near the “Choose File” option on the Import page.

If the import file is too large, then it interrupts to skip the number of queries during the import.

It is better to process import via Terminal if the import file exceeds the allowed limit. It will prevent the data inconsistency that may occur because of the partial import.

Note the below Terminal command to process importing larger SQL files.

#path-to-mysql#mysql -u root -p #database_name# < #path-of-the-sql-file#

Replace the following variable in the above command

• #path-to-mysql# – Path where the MySQL is. Example: /Applications/XAMPP/bin/mysql
• #database_name# – The target database where the import is going to happen.
• #path-of-the-sql-file# – The path of the source SQL to import. Example: /Users/vincy/Desktop/db_phppot_example.sql

The command line execution is also used to connect the remote server. It is in case of facing restrictions to access a remote MySQL server via phpMyAdmin.

Features of the phpMyAdmin Import

The phpMyAdmin “Import” functionality provides several features.

• It allows the import of files in the following formats. The default format is SQL.
  
  • CSV
  • ESRI shape file
  • MediaWiki table
  • OpenDocument spreadsheet
  • SQL
  • XML
• It allows choosing character sets and SQL compatibility modes.
• It allows partial imports by allowing interruptions during the import of larger files.

Things to remember

When you import a database or table certain things to remember.

Database resource “Already exists” error

This error will occur if the importing file contains statements of existing resources.

Example:
If the importing file has the query to create an existing table, then phpMyAdmin will show this error.

So, it is important to clean up the existing state before importing a database to avoid this problem.

Access denied error

If the users have no permission to import or create databases/tables, then it will return this error.

If the user can import and can’t create tables, the file must contain allowed queries only.

Note: If you are importing via remote access, give the right credentials to connect. Make sure about the user access privileges to import or related operations.

↑ Back to Top

Summary: To split a string by a number, use the regex split method using the “\d” pattern.

Minimal Example

my_string = "#@1abc3$!*5xyz" # Method 1

import re res = re.split('\d+', my_string)

print(res) # Method 2

import re res = re.findall('\D+', my_string)

print(res) # Method 3

from itertools import groupby li = [''.join(g) for _, g in groupby(my_string, str.isdigit)]

res = [x for x in li if x.isdigit() == False]

print(res) # Method 4

res = []

for i in my_string: if i.isdigit() == True: my_string = my_string.replace(i, ",")

print(my_string.split(",")) # Outputs:

# ['#@', 'abc', '$!*', 'xyz']

Problem Formulation

Problem: Given a string containing different characters. How will you split the string whenever a number appears?

Method 1: re.split()

The re.split(pattern, string) method matches all occurrences of the pattern in the string and divides the string along the matches resulting in a list of strings between the matches. For example, re.split('a', 'bbabbbab') results in the list of strings ['bb', 'bbb', 'b'].

Code:

import re

my_string = "#@1abc3$!*5xyz"

res = re.split('\d+', my_string)

print(res) # ['#@', 'abc', '$!*', 'xyz']

Explanation: The \d special character matches any digit between 0 and 9. By using the maximal number of digits as a delimiter, you split along the digit-word boundary. 

Method 2: re.findall()

The re.findall(pattern, string) method scans string from left to right, searching for all non-overlapping matches of the pattern. It returns a list of strings in the matching order when scanning the string from left to right.

Code:

import re

my_string = "#@1abc3$!*5xyz"

res = re.findall('\D+', my_string)

print(res) # ['#@', 'abc', '$!*', 'xyz']

Explanation: The \D special character matches all characters except any digit between 0 and 9. Thus, you are essentially finding all character groups that appear before the occurrence of a digit.

Do you want to master the regex superpower? Check out my new book The Smartest Way to Learn Regular Expressions in Python with the innovative 3-step approach for active learning: (1) study a book chapter, (2) solve a code puzzle, and (3) watch an educational chapter video.

Method 3: itertools.groupby()

Code:

from itertools import groupby

my_string = "#@1abc3$!*5xyz"

li = [''.join(g) for _, g in groupby(my_string, str.isdigit)]

res = [x for x in li if x.isdigit() == False]

print(res) # ['#@', 'abc', '$!*', 'xyz']

Explanation:

• The itertools.groupby(iterable, key=None) function creates an iterator that returns tuples (key, group-iterator) grouped by each value of key. We use the str.isdigit() function as key function.
• The str.isdigit() function returns True if the string consists only of numeric characters. Thus, you will have a list created by using numbers as separators. Note that this list will also contain the numbers as items within it.
• In order to eliminate the numbers, use another list comprehension that checks if an element in the list returned previously is a digit or not with the help of the isdigit method. If it is a digit, the item will be discarded. Otherwise it will be stored in the list.

Method 4: Replace Using a for Loop

Approach: Use a for loop to iterate through the characters of the given string. Check if a character is a digit or not. As soon as a digit is found, replace that character/digit with a delimiter string ( we have used a comma here) with the help of the replace() method. This basically means that you are placing a particular character in the string whenever a number appears. Once all the digits are replaced by the separator string, split the string by passing the separator string as a delimiter to the split method.

Code:

my_string = "#@1abc3$!*5xyz"

res = []

for i in my_string: if i.isdigit(): my_string = my_string.replace(i, ",")

print(my_string.split(",")) # ['#@', 'abc', '$!*', 'xyz']

Conclusion

Phew! We have successfully solved the given problem and managed to do so using four different ways. I hope you found this article helpful and it answered your queries. Please subscribe and stay tuned for more solutions and tutorials.

Happy coding!

Related Read: How to Split a String Between Numbers and Letters?