Hi, this is a presentation on using the new MySQL Shell with Python and I'd like to start by introducing myself. My name is Dave Stokes. I'm a community manager. I've been on the MySQL community team for just over a decade using MySQL since it first came out, and this will hopefully be a quick introduction to the MySQL shell and how to extend it with Python. Safe harbor statement I work for Oracle. During the course of this presentation I'm talking about the free open source version of MySQL and the MySQL Shell all out there under the GPL. However, in Q-A-I might very often some new functionality coming in future releases since I don't have perfect knowledge of those perfect releases and what I know is probably wrong. Take anything I say about a future release with a grain of salt. So about me once again I'm on the MyScope community team. I live in Texas. The slides would be available@slideshare.net Slash Dave Stokes I got a blog at elephantdolphin blogspot.com. If you need to get a hold of me, I'm at Stoker on Twitter or David stokes@oracle.com so what is the new MySQL Shell? Well, if you used our old interface, anything before 80 it was a simple commandline editor. The new shell is much more advanced in that it allows you to process queries in structured query language. Big surprise there, eh? Also there's a JavaScript mode and a Python mode. So between these three modes you've got a lot of flexibility. And by the way, if you have something that's written in Python, you can run it in JavaScript modes, and JavaScript modes stuff can run in Python. So if you haven't seen our new shell, it's no longer monochromatic when you fire it up. It of course gives you the copyright. And if you notice here, this yellow stuff here says js for JavaScript. Well what if you don't like JavaScript and you'd want to use Python? Py moves you into Python mode. And if you're an old timer like me and you want SQL, there you go. You can run SQl by the way, you can run SQL code from Python or JavaScript mode as you wish. Now once you log in, if you do a backslash s for status or you can do a session get status, there's some things here I like to point out to you. First thing, if you can see my mouse here is by default we're secure we're using SSL with TLS AES 256 by default. If you don't need it, you can turn it off, but by default we wanted to give that to you. Also, our default character set now is UTF eight mb four. That's to help get all four planes of the 90 unit code standard, which means yes, you do get emojis. Yay. And by the way, if you notice down here, compression, if you need to compress stuff, that helps a lot sound by default. So with the new shell you have two types of sessions. The classic, which is our old port three three six. We've had that for 25 years. Yes, MySQL is 25 year old. And we also have another port 3360 for the new Xdev API, which is designed to handle both SQL traffic and NoSQL traffic. You can use MySQL as a NoSQL JSON document store. By the way, you can reach the JSON stuff from the classic side and the classic stuff from the NoSQL side. Trying to give you more flexibility. Now the reason we're here today is I want to talk about the functions. If you write functions or programs or scripts or whatever you want to call it, in either JavaScript or Python, and you find them handy, you can actually run them under different sessions. So you can have one session running JavaScript, two more running Python, run all sorts of reports and all that. Now to log in you can either type in MySQL shell, root at localhost or whatever thing. Or once you get the shell fired up, you can do a backslash c for connect and you log in. Or once you get fired up you can have multiple sessions. Now this example is in JavaScript where we're having session one, which we say we're going to do a classic session which will work to three, three six, account name and password. Now if I want a second session to generate data and feed it to the first session, no problem there. Oh, by the way, if you want encryption, you can specify how you want it, where you want your definitions for all the stuff. In this example we're accepting TLS eleven and one two. Well, by default compression is turned on, but if you want to specify your algorithms like LZ four or Z standard and the local compression, you can do that. Also, something we can offer for you is JSON schema validation. JSon by default is very loosey goosey. There's no rigor on there. Traditionally, relational databases is very tightly structured. One thing we do, thanks to the wonderful, wonderful people@jsonschema.org, is we can set up an exemplar template that we test your incoming JSON documents against and we can say hey, we have this required field and the values, it takes numbers and the minimum value is -90 the maximum number 90. Maximum is plus 90. So you can set up required fields range check and type checks. Very very handy. By the way, first time you log in using the MySQL shell, it will ask you if you want to store your password that is hashed very securely. So if you don't want to keep typing your password, that will do that work for you too. By the way, if you have some code in Python, you can just pipe it with a shell and away it will go. Make sure you're in Python mode of course. By the way, if you have libraries you like, like this example, I'm using datetime. I import the datetime library and I get full access to that. So if you have libraries you'd like to work with the data with, it's all there for you. Also, if you're using our new Xdev API as a NoSQL document store, the MySQL no longer requires you to actually go out and use SQL if you don't want to. You'll find this is a very straightforward API. On our system, we're connected to a database called WorldX. It has a document collection. We've typed get collection. It'll tell you the name of that collection is country info. So the current database pointer to that collection, we're going to find all the records that have a name like a parameter and we bind uppercase s wildcard to that parameter and we're going to limit to one. So our new API is very straightforward, very easy to use, very similar between all three languages. Also supports in our other various connectors, very easy to use. Once again, if you don't like SQL, you do mysql without SQL. By the way, I'm going to repeat that. You find out your collections, go out and run your query. By the way, it is not an Orm, it is actually a totally clean sheet protocol written on Google protobuffs. Real nice design. And by the way has a lot of smarts put in there so it gets rid of things like most SQL injection. Okay, here's another thing. I'm logged in. I'm in Python mode. For my current session, I'm going to run underscore SQL on just a straight query. So that'd be the same as going out to the SQL mode and running the same query by the way, if you notice this is out in a table output. If you want to do it in JSon you tell your shell hey, my resort format is going to be JSON and how it comes in JSOn very very handy. By the way, I mentioned earlier, you don't need to use SQl say like you have a project where your bosses tell you hey, go ahead and start coding, we'll tell you what you're actually doing next week. It's real easy to go out there and create a document collection and in there we're going to add a record and haven't gone, had to go out and set up relations, had to set up indexes. I'm able to just store data right off the bat. Now if I do a DB openjs the collection we create up here and in a record you'll notice that when I do the find command that it gives you an underscore id. This is the value you can specify or have the server generate it for you. This is actually a primary key for the NoDB storage engine. Very important, but I'm not going to go off into that. So if you have JavaScript libraries, this is an example of JavaScript libraries. If I type in the libraries require a file. If I do Dave tab, it shows me the two functions we have defined over here and I can run them at will. That's about the same thing for JavaScript. Now you can define your own extensions to the base functionality of the shell. It forms their reports and extensions. Now these can be created once again in JavaScript or Python, and they're accessible from either of those languages. You can persist these, you can watch them, have them run over and over again, and they are automatically loaded every time the shell starts. Now here's an interesting example. We're going to define a quick little extension in Python. And the trick down here is we create a function, this one where I'm just going to call show tables and we have to make sure that we have a session. You have to be logged into the database to actually get access to the tables. So if you don't have a session it kind of tells you where to go. So if you do have a session, it's basically going to run underscore SQL, select from world city and then dump out the results. Now the trick is to register the shell is we have to have a plugin object and we create an extension for that. And for that plugin object that we have here, we give it the name of our function, what we want to call it, and a little bit of help information as you can see there in what, a dozen lines of code. Very easy to set up a basic loop query. So once again, make sure we have a session, and once we have a session we can run queries all day long. Now as I mentioned before, all we have to do is register the plugin object with our function and easy peasy. Now reports are a little bit different. You can register a report that you define in any Python or JavaScript. The reporting facility handles building reports. There's a whole bunch of out there already for you. And reports can specify a list of report specific options. If you want to have a v for verbose, z for compress, however you want to do it and you could have all sorts of inputs so it actually read from the screen. Now instead of registering a plugin, it's register report. Otherwise it's pretty much like you've seen earlier. You can write your own reports. This is one where it modes out there and watches the sessions. I believe this one's actually built into the new shell and when you run it, it tells you I'm running this with a slash watch sessions which is the name of our report and it runs every 2 seconds. When the building reports you can set up intervals, you can set up global statuses to watch. You can run things in foreground background however you want to do it. Now the shell by default also has some utilities. For those of you who deal with a lot of data. One very important one is import JSON. So if you have a lot of JSoN it's very easy to use. You say util import JSoN, the name of the file, what schema or database you're loading into, and what you want to call the collection. This works with the latest version of the shell. Also for those of you who are on five seven wanting to go to 80, we have a uterus check for server upgrade. This will go out there and make sure that you're not using reserved words. We have several new reserved words in 80. Also make sure that you're not going to run into any problems like converting over to utf eight and before. Also we can have utilities for importing tables. We have a lot of interesting stuff off the import table. If you've been playing with MySQl dump for years and years and years, are looking for something a little bit faster. We have export table and that's a very fast way to dump your data. And we also have the ability to dump in the entire instance. So if you want everything on the server, you can do that. If you want to test to see how long it's going to run, you can do that. If you're using the Oracle cloud, there's some functionality you can do there for setting up buckets and all that. If you want to dump tables, you can just say okay, from this schema, I want these two tables. Very interesting. Fun. Now if you want to learn more about all this, the MySQL Shell is actually fairly well documented and there's lots of examples on writing plugins. This one, developpaper.com is a very good starting place. My counterpart from Belgium has a lot of interesting blogs about how to do that. Also he has a GitHub libraries of a whole bunch of shell plugins that are very nice, very very handy things like that. You copy users, check in ODB status and all other stuff. And before I go, I'd like to say, hey, if you want to run the MySQL shell under our new MySQL database service, if you go to this location, you can get $300 in credits. This is quite a long time, month or two if you stay in the low end engines for free to go out there and test the MySQL service. By the way, this is the full enterprise version of MySQL Eight and it is the latest and greatest, and it is monitored and supported by MySQL engineers. Also, if you're a member of the MySQL community, congratulations, you're now a member of the MySQL community. Please join us on MySQL community manager on mySql.com under Twitter we're MySQl, Facebook we're MySQL and LinkedIn Render MySQL. By the way, if you are a startup and startups only, you can enroll@oracle.com. Slash startup get a whole bunch of cloud credits, whole bunch of help with other areas, exposure with marketing, some good stuff if you want to do some events, because events are coming back. Thank goodness. This is a great thing if you are a startup because Oracle really wants to get you off the ground. And by the way, if you're using a lot of JSON with MySQL, please buy a copy of my book. This is the second edition. First edition came out three years ago and we keep adding new features. So a second edition is new. This is for folks who want to learn how to use JSON with a database. Our manual is good, but it's a reference guide, not a coding example. And this is how you do it best. Practices guide available on Amazon today. And with that, we're going to go into Q A, and I want to thank you for your time.