Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Python Syntax

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Python Syntax

      The MySQL-Python connector specifically refers to a library in Python that enables communication between a Python program and a MySQL database. It acts as a bridge, allowing Python programs to interact with and manipulate data stored in a MySQL database. Essentially, the MySQL-Python connector simplifies the process of connecting, querying, and managing databases, enabling developers to seamlessly integrate their Python applications with MySQL databases.

      Installing "python-mysql" connector

      To use MySQL with Python, you typically need to install a MySQL connector or library. Here are the general steps to install it
      Step 1: Install MySQL Server
      Make sure you have MySQL Server installed on your machine or have access to a remote MySQL server.
      Step 2: Install MySQL Connector for Python
      Open a command prompt or terminal and use the following command to install the MySQL Connector for Python using pip, which is the package installer for Python:
      pip install mysql-connector-python
      If you are using Python 3, you might need to use 'pip3' instead of 'pip'.
      Step 3: Verify Installation
      After the installation is complete, you can verify that the library is installed by opening a Python interactive shell and trying to import the connector:
      import mysql.connector

      Python Functions to Access MySQL

      When working with MySQL in Python, the 'mysql-connector-python' library provides various functions to interact with a MySQL database. Here are some important functions commonly used
      S.No
      Function & Description
      1
      connect()
      Establishes a connection to the MySQL server.
      2
      cursor()
      Creates a cursor object to execute SQL queries.
      3
      execute(query, params=None)
      Executes a SQL query. 'params' is an optional parameter for query parameters.
      4
      fetchone()
      Fetches the next row from the result set.
      5
      fetchall()
      Fetches all rows from the result set.
      6
      commit()
      Commits the current transaction to the database.
      7
      rollback()
      Rolls back the current transaction, undoing any changes since the last commit.
      8
      close()
      Closes the cursor and the connection to the database.
      9
      executemany()
      Executes a SQL command against all parameter sequences in the provided list.

      Basic Example

      To connect and communicate with a MySQL database using Python, you can follow these steps
      • Use 'pip install mysql-connector-python' to install the MySQL Connector for Python.
      • Import the MySQL Connector module in your Python script: "import mysql.connector".
      • Create a connection using "mysql.connector.connect()" with your database details.
      • Create a cursor using "connection.cursor()".
      • Use the cursor's "execute()" method to run SQL queries.
      • If applicable, use "fetchone()" or "fetchall()" to retrieve query results.
      • If you modify data, commit changes using "connection.commit()".
      • Close the cursor and connection with "cursor.close()" and "connection.close()".
      The following example shows a generic syntax of a Python program to call any MySQL query 
      import mysql.connector
      # Establish connection
      connection = mysql.connector.connect(host='localhost', user='user', password='pass', database='db')
      # Create cursor
      cursor = connection.cursor()
      # Execute query
      cursor.execute("SELECT * FROM table")
      # Fetch and print results
      rows = cursor.fetchall()
      print(rows)
      # Close cursor and connection
      cursor.close()
      connection.close()