def connect(): return mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******' })
def connect_with_mycnf(use_mysqlx=False, file=None): try: import configparser except: print("ERROR: Module Python configparser must be installed") return if not file: try: from pathlib import Path file = str(Path.home()) + "/.my.cnf" except: print( "ERROR: Module Pathlib must be installed or provide a filename" ) return import getpass print("let's use info from %s to connect" % file) config = configparser.ConfigParser() config.read(file) if not config.has_section('client'): print("ERROR: your my.cnf file should contain a section '[client]'") return user = config['client'].get('user', getpass.getuser()) hostname = config['client'].get('host', 'localhost') password = config['client'].get('password', '') import mysqlsh shell = mysqlsh.globals.shell if use_mysqlx: scheme = "mysqlx" port = config['client'].get('port', '33060') connection_dict = { "scheme": scheme, "user": user, "password": password, "host": hostname, "port": port } session = mysqlx.get_session(connection_dict) else: scheme = "mysql" port = config['client'].get('port', '3306') connection_dict = { "scheme": scheme, "user": user, "password": password, "host": hostname, "port": port } session = mysql.get_session(connection_dict) shell.set_session(session) return
def connect_with_mycnf(use_mysqlx=False, file=None): """ Connect to MySQL using old .my.cnf file. This function reads the login information from .my.cnf. Args: use_mysqlx (bool): Optional boolean value to use or not MySQL X protocol (default: false). file: The optional location of the my.cnf file (default: ~/.my.cnf). """ try: import configparser except: print("ERROR: Module Python configparser must be installed") return if not file: try: from pathlib import Path file=str(Path.home()) + "/.my.cnf" except: print("ERROR: Module Pathlib must be installed or provide a filename") return import getpass print("let's use info from %s to connect" % file) config = configparser.ConfigParser() config.read(file) if not config.has_section('client'): print("ERROR: your my.cnf file should contain a section '[client]'") return user=config['client'].get('user', getpass.getuser()) hostname=config['client'].get('host', 'localhost') password=config['client'].get('password', '') import mysqlsh shell = mysqlsh.globals.shell if use_mysqlx: scheme = "mysqlx" port=config['client'].get('port', '33060') connection_dict = { "scheme": scheme, "user": user, "password": password, "host": hostname, "port": port } session = mysqlx.get_session(connection_dict) else: scheme = "mysql" port=config['client'].get('port', '3306') connection_dict = { "scheme": scheme, "user": user, "password": password, "host": hostname, "port": port } session = mysql.get_session(connection_dict) shell.set_session(session) return
def ensure_session(): global testSession global session if testSession is None: print("Creating session...\n") testSession = mysqlx.get_session(__uripwd) # Ensures the user on dev-api exists try: testSession.sql("create user mike@'%' identified by 'paSSw0rd'").execute() testSession.sql("grant all on *.* to mike@'%' with grant option").execute() except: pass else: print("Session exists...\n") session = testSession
def ensure_session(): global testSession global session if testSession is None: print "Creating session...\n" testSession = mysqlx.get_session(__uripwd) # Ensures the user on dev-api exists try: testSession.sql("create user mike@'%' identified by 'paSSw0rd'").execute() testSession.sql("grant all on *.* to mike@'%' with grant option").execute() except: pass else: print "Session exists...\n" session = testSession
# Assumptions: ensure_schema_does_not_exist # Assumes __uripwd is defined as <user>:<pwd>@<host>:<plugin_port> from __future__ import print_function from mysqlsh import mysqlx mySession = mysqlx.get_session(__uripwd) ensure_schema_does_not_exist(mySession, 'js_shell_test') schema = mySession.create_schema('js_shell_test') mySession.set_current_schema('js_shell_test') result = mySession.sql('create table js_shell_test.buffer_table (name varchar(50) primary key, age integer, gender varchar(20))').execute() #@<> SqlResult member validation validate_members(result, [ 'execution_time', 'warning_count', 'warnings_count', 'warnings', 'get_execution_time', 'get_warning_count', 'get_warnings', 'column_count', 'column_names', 'columns', 'get_column_count', 'get_column_names', 'get_columns', 'fetch_one', 'fetch_one_object', 'fetch_all',
from mysqlsh import mysqlx # Connect to server session = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******' }) # Get the Schema test db = session.get_schema('test') # Create a new collection myColl = db.create_collection('my_collection') # Start a transaction session.start_transaction() try: myColl.add({ 'name': 'Jack', 'age': 15, 'height': 1.76, 'weight': 69.4 }).execute() myColl.add({'name': 'Susanne', 'age': 24, 'height': 1.65}).execute() myColl.add({ 'name': 'Mike', 'age': 39, 'height': 1.9, 'weight': 74.3
def createTestTable(session, name): # use escape function to quote names/identifier quoted_name = session.quote_name(name) session.sql("DROP TABLE IF EXISTS " + quoted_name).execute() create = "CREATE TABLE " create += quoted_name create += " (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)" session.sql(create).execute() return session.get_current_schema().get_table(name) from mysqlsh import mysqlx session = mysqlx.get_session('mike:paSSw0rd@localhost:33060/test') default_schema = session.get_default_schema().name session.set_current_schema(default_schema) # Creates some tables table1 = createTestTable(session, 'test1') table2 = createTestTable(session, 'test2')
from mysqlsh import mysqlx # Direct connect with no client side default schema defined mySession = mysqlx.get_session('mike:paSSw0rd@localhost') mySession.set_current_schema("test")
# Working with Relational Tables from mysqlsh import mysqlx # Connect to server using a connection URL mySession = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******'} ) myDb = mySession.get_schema('test') # Accessing an existing table myTable = myDb.get_table('my_table') # Insert SQL Table data myTable.insert(['name','birthday','age']) \ .values('Sakila', mysqlx.date_value(2000, 5, 27), 16).execute() # Find a row in the SQL Table myResult = myTable.select(['_id', 'name', 'birthday']) \ .where('name like :name AND age < :age') \ .bind('name', 'S%') \ .bind('age', 20).execute() # Print result print myResult.fetch_all()
# -*- coding: utf-8 -*- """ Created on Tue Jan 21 10:52:48 2020 @author: Ador """ <<<<<<< HEAD ======= >>>>>>> 002815afbd6007dd598bf7d0807f2d84dd2d15b9 # Connecting to MySQL Server and working with a Collection from mysqlsh import mysqlx # Connect to a Server mySession = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******'} ) myDb = mySession.get_schema('world_x') # Create a new Collection myColl = myDb.create_collection('magasin') # Insert documents myColl.add({'_id': '1', 'couleur': 'Bleu', 'Marque': 'BMW', 'Model': 'F800 GS'}).execute() myColl.add({'_id': '2', 'couleur': 'gris', 'Marque': 'Honda', 'Model': '2650 ABS'}).execute() # Find a document docs = myColl.find('couleur like: param1 AND Model like : param2').limit(1).bind('param1', 'B%').bind('param2', 'F%').execute() # Print document
# Assumptions: ensure_schema_does_not_exist is available # Assumes __uripwd is defined as <user>:<pwd>@<host>:<plugin_port> from __future__ import print_function from mysqlsh import mysqlx #@<> Session: validating members mySession = mysqlx.get_session(__uripwd) validate_members(mySession, [ 'close', 'commit', 'create_schema', 'drop_schema', 'get_current_schema', 'get_default_schema', 'get_schema', 'get_schemas', 'get_uri', 'help', 'is_open', 'set_current_schema', 'set_fetch_warnings', 'quote_name', 'rollback', 'run_sql', 'start_transaction', 'sql', 'default_schema', 'uri', 'current_schema', 'set_savepoint',
# La fonction mashup def mashup(myDb): # Create a new collection 'my_collection' myColl = myDb.get_collection('Moto') # Accessing an existing table myTable = myDb.get_table('MODELS') # Find objects from document objs = myColl.find().execute() for obj in objs.fetch_all(): for name in obj.data.name: print("INSERT INTO MODELS (Models) VALUES ('" + name + "')") # Insert SQL Table data myTable.insert(['Models']).values(name).execute() # Connect to server mySession = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******' }) myDb = mySession.get_schema('magasin') mashup(myDb)
""" # Connecting to MySQL Server and working with a Collection from mysqlsh import mysqlx # La fonction mashup def mashup(myDb): # Create a new collection 'my_collection' myColl = myDb.get_collection('photographie') # Accessing an existing table myTable = myDb.get_table('IMAGES') # Find objects from document objs = myColl.find().execute() for obj in objs.fetch_all(): for description in obj.description: print("INSERT INTO IMAGES (Images) VALUES ('"+description+"')") # Insert SQL Table data myTable.insert(['Images']).values(description).execute() # Connect to server mySession = mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******'} ) myDb = mySession.get_schema('widesign') mashup(myDb)
def connect(): return mysqlx.get_session( { 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******'} )
# Assumptions: ensure_schema_does_not_exist is available # Assumes __uripwd is defined as <user>:<pwd>@<host>:<plugin_port> # validateMemer and validateNotMember are defined on the setup script from mysqlsh import mysqlx #@ Session: validating members mySession = mysqlx.get_session(__uripwd) all_members = dir(mySession) # Remove the python built in members mySessionMembers = [] for member in all_members: if not member.startswith('__'): mySessionMembers.append(member) validateMember(mySessionMembers, 'close') validateMember(mySessionMembers, 'create_schema') validateMember(mySessionMembers, 'get_current_schema') validateMember(mySessionMembers, 'get_default_schema') validateMember(mySessionMembers, 'get_schema') validateMember(mySessionMembers, 'get_schemas') validateMember(mySessionMembers, 'get_uri') validateMember(mySessionMembers, 'set_current_schema') validateMember(mySessionMembers, 'set_fetch_warnings') validateMember(mySessionMembers, 'sql') validateMember(mySessionMembers, 'default_schema') validateMember(mySessionMembers, 'uri') validateMember(mySessionMembers, 'current_schema') validateMember(mySessionMembers, 'set_savepoint') validateMember(mySessionMembers, 'release_savepoint') validateMember(mySessionMembers, 'rollback_to')
# La fonction mashup def mashup(myDb): # Create a new collection 'my_collection' myColl = myDb.get_collection('instruments') # Accessing an existing table myTable = myDb.get_table('INSTRUMENTS') # Find objects from document objs = myColl.find().execute() for obj in objs.fetch_all(): for instruments in obj.music.instruments: print("INSERT INTO INSTRUMENTS (Name) VALUES ('" + music.instruments + "')") # Insert SQL Table data myTable.insert(['Name']).values(music.instruments).execute() # Connect to server mySession = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******' }) myDb = mySession.get_schema('musicshop') mashup(myDb)
# Assumptions: ensure_schema_does_not_exist is available # Assumes __uripwd is defined as <user>:<pwd>@<host>:<plugin_port> from mysqlsh import mysqlx mySession = mysqlx.get_session(__uripwd) ensure_schema_does_not_exist(mySession, 'py_shell_test') schema = mySession.create_schema('py_shell_test') mySession.set_current_schema('py_shell_test') result = mySession.sql('create table table1 (name varchar(50))').execute() table = mySession.get_schema('py_shell_test').get_table('table1') #@ Testing table name retrieving print 'get_name(): ' + table.get_name() print 'name: ' + table.name #@ Testing session retrieving print 'get_session():', table.get_session() print 'session:', table.session #@ Testing table schema retrieving print 'get_schema():', table.get_schema() print 'schema:', table.schema #@ Testing existence print 'Valid:', table.exists_in_database() mySession.sql('drop table table1').execute()
# column list, user data structure COLUMNS = [ 'id_caixer', 'dni_caixer', 'nom_caixer', 'cognom_caixer', 'ntelf_caixer' ] user_info = { 'host': 'localhost', 'port': 33060, 'user': '******', 'password': '******', } print("Listing 4-6 Example - Python X DevAPI Demo with Relational Data.") # Get a session (connection) my_session = mysqlx.get_session(user_info) # Precautionary drop schema my_session.drop_schema('botiga') # Create the database (schema) my_db = my_session.create_schema('botiga') # Execute the SQL statement to create the table sql_res = my_session.sql(CREATE_TBL).execute() # Get the table object my_tbl = my_db.get_table('caixer') # Insert some rows (data) my_tbl.insert(COLUMNS).values(123, "12366677Z", "Roser", "Avellan", "934524565").execute() my_tbl.insert(COLUMNS).values(54, "14366677W", "Fran", "Català", "932457825").execute() my_tbl.insert(COLUMNS).values(34, "11363547X", "Pau", "Barber", "938742595").execute()