def connect():
return mysqlx.get_session({
'host': 'localhost',
'port': 33060,
'user': '******',
'password': '******'
})
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')
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')
# 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()